# Migrating database connection

## About this task

After setting up a database connection in the Metadata folder and reuse it in your Jobs, if you want to change the database type from one to another, you can migrate the source database connection to a target connection if the target connection is also set up in the Metadata folder.

Information noteNote: This feature applies to data integration Jobs only. The HBase, Hive, Impala, Iceberg, and MapR-DB database types cannot be migrated.

## Procedure

1. Expand Metadata > Db Connections in the Repository tree view, right-click the database connection you want to migrate and select Migrate Db connection to... from the contextual menu.

   !["Migrate Db connection to..." context menu.](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/migrate-db-connection-context-menu.png)
2. The database migration wizard opens.

   !["Database connection migration - Step 1/4" dialog box.](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/migrate-db-connection-step1.png)

   In the Source column of the wizard, the source connection and schemas retrieved from it are shown with the number of Jobs they are reused in. In the Target column, select the database connection you want to migrate. The schemas in the source connection that exist in the target connection with the same name (case insensitive) are marked with the ![OK](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/ok.svg) icons. Click Next to go to the next step.
3. All Jobs in the closed state that use the source connection are displayed in the wizard.

   !["Database connection migration - Step 2/4" dialog box.](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/migrate-db-connection-step2.png)

   A database component is migratable if the following conditions are met:
   * The component reuse the database connection in the repository. If the Property Type of the component is set to Built-in, the component will not be detected and thus not changed.
   * The schema reused in the component exists in the target connection with the same name (case insensitive).
   * The component has an equivalent in the target database.
   * The ELT components are migratable if the last Map component in the subJob use a repository connection. If the property type of the last Map component is set to Built-in, all the ELT components in the subJob will not be detected.

     Information noteNote: For ELT UniteMap cases, only migration between Oracle and Teradata databases is supported.

   Each component is marked with a ![OK](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/ok.svg) or ![Warning](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/warning_small.svg) icon indicating if it is migratable or not. If a component is migratable and it reuses a schema in the source connection, the schema name is shown after the component name. If a component is not migratable, the reason is highlighted. A Job is migratable only if all detected components in it are migratable.

   If the component reuses a schema in the source connection and the table name is the same as the schema name, after the migration, the table name will be changed to schema name in the target connection. For example, the schema table\_1 in the source connection matches TABLE\_1 in the target connection. If a component reuse this schema and its table name is also table\_1, after the migration, the table name will be changed to TABLE\_1. Note that Talend Studio does not validate columns in target table.

   If a component uses the source connection but it does not use the schema from this connection, the component will be migrated to the target connection but the schema and table name will not be changed.

   For example, to migrate from a MySQL database connection to Oracle:
   | Source connection | Target connection |  |  |
   | --- | --- | --- | --- |
   | Name | Schemas | Name | Schemas |
   | source\_conn (MySQL) | table\_1 | target\_conn (Oracle) | TABLE\_1 |
   | table\_2 | TABLE\_2 |  |  |
   | table\_3 | TABLE\_3 |  |  |
   | table\_4 |  |  |  |

   The following table shows the migratable and not migratable Jobs/components and the schema types and table names before and after the migration.
   | Migration cases | Before migration | Is component Migratable | Is Job Migratable | After migration |  |  |  |  |  |  |
   | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
   | Job | Component | Property type | Schema type | Table name |  |  | Component | Property type | Schema type | Table name |
   | Job1 | tMysqlInput | source\_conn | source\_conn-table\_1 | table\_1 | Yes | Yes | tOracleInput | target\_conn | target\_conn-TABLE\_1 | TABLE\_1 |
   | tMysqlOutput | source\_conn | source\_conn-table\_2 | table\_2 | Yes | tOracleOutput | target\_conn | target\_conn-TABLE\_2 | TABLE\_2 |  |  |
   | tMysqlBulkExec | source\_conn | source\_conn-table\_3 | table\_3 | Yes | tOracleBulkExec | target\_conn | target\_conn-TABLE\_3 | TABLE\_3 |  |  |
   | tMysqlOutputBulkExec | source\_conn | source\_conn-table\_3 | table\_X | Yes | tOracleOutputBulkExec | target\_conn | target\_conn-TABLE\_3 | table\_X |  |  |
   | tMysqlSP | source\_conn | built-in/generic | table\_Y | Yes | tOracleSP | target\_conn | built-in/generic | table\_Y |  |  |
   | tMySQLValidRows | built-in | built-in/generic | table\_Z | N/A | tMySQLValidRows | built-in | built-in/generic | table\_Z |  |  |
   | tMysqlInput | built-in | built-in | table\_A | N/A | tMysqlInput | built-in | built-in | table\_A |  |  |
   | tMysqlInput | built-in | source\_conn-table\_3 | table\_3 | N/A | tMysqlInput | built-in | source\_conn-table\_3 | table\_3 |  |  |
   | Job2 | tMysqlInput | source\_conn | source\_conn-table\_1 | table\_1 | Yes | No |  |  |  |  |
   | tMysqlOutput | source\_conn | source\_conn-table\_4 | table\_4 | No |  |  |  |  |  |  |
   | tMysqlTableList | source\_conn |  |  | Yes |  |  |  |  |  |  |
   | tMysqlColumnList | source\_conn |  |  | No |  |  |  |  |  |  |
   | Job3 | tELTMysqlInput |  | source\_conn-table\_1 | table\_1 | Yes | Yes | tELTOracleInput |  | target\_conn-TABLE\_1 | TABLE\_1 |
   | tELTMysqlMap\_1 |  |  |  | Yes | tELTOracleMap |  |  |  |  |  |
   | tELTMysqlMap\_2 | source\_conn |  |  | Yes | tELTOracleMap | target\_conn |  |  |  |  |
   | tELTMysqlOutput |  | built-in/generic | table\_X | Yes | tELTOracleOutput |  | built-in/generic | table\_X |  |  |

   For CDC use cases, to migrate from one database to another, both the connection to the source data warehouse and to the CDC dedicated database must have target connections set up in the repository. The migration should be triggered from the CDC dedicated database connection.

   You can use the search field to filter the Jobs or select from the drop down list to show all migratable and not migratable Jobs. Click Next to go to the next step.
4. A summary of migratable Jobs is shown in the wizard. Click Apply to perform the migration.

   !["Database connection migration - Step 3/4" dialog box.](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/migrate-db-connection-step3.png)
5. The migration report page is shown. You can click the Check the report link to open the report, or click the Browse button to go to the <Studio\_Home>\workspace\report\massUpdate directory where the report is saved.

   !["Database connection migration - Step 4/4" dialog box.](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/migrate-db-connection-step4.png) The migration report is in CSV format containing the following information of the migrated Jobs: ![Database connection migration report.](https://help.qlik.com/talend/en-US/studio-user-guide/Resources/images/migrate-db-connection-report.png)