DB Migration using SQLgateway
Tags: tutorial, sql, docker, programming
Published: 2022-07-18 19:27:29

Thanks to Yuri Gomes we have seen a very nice example for [DB migration from Postgres to IRIS](https://openexchange.intersystems.com/package/migration-pg-iris-dataset).  
My personal <span>problem is the use of DBeaver as a migration tool.<br />Especially as one of the strengths of IRIS ( and also Caché) before is the availability of the<br />SQLgateways that allow access to any external Db as long as for them an access usinig <br />JDBC or ODBC is available. So I extended the package to demonstrate this.</span>

<!--break-->

It is a classic OEX package with a complete Docker.  
The SQLgateway is installed during Docker build and the required jdbcdriver for Linux  
is included in this repo In order to make this demo faster, the size of tables to migrate  
have been shrunk a bit. 

### How to test

All migration actions can be executed directly from SMP.

1.     
Verify the gateway connection in       
<span style="color:#0000ff;">SMP> Administration> Configuration >Connectivity >SqlGateway_Configuration     </span>
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zwrp9t9efkepm2817z4t.jpg)</span>  
  
To test Connection click **edit**  
  

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nxct6uylhtqece3o7b8p.jpg) <span> </span>  
  
and **Test Connection     **  
  

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rh7o6xox9g7rbfgjeygi.jpg) <span> </span>  
  
- verify **Connection successful     **  
- Be patient at this point. Postgres Containers sometimes take quite some time to talk to you.      
- Wait a little bit, reload the page in browser and try the test again.     

2.  
Identifying the source tables. In SMP > Change to Namespace USER then step to       
<span style="color:#0000ff;">SMP >Explorers >SQL >Wizards > Data Migration    </span>![]
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qom0ein9ed64sl1xe8qu.jpg) <span> </span>

3.       
Set required import parameters           

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ezwfeineocb9tgn7745e.jpg)  
  
- Destination Namespace         
- Type = TABLE        
- Gateway = postgres      >>>  now the first connection is established and you select               
- Schema = public                 
- Tables to migrate = all         

4.        
Identify target but change schema to be OEX compatible from **public** to **dc_public**      
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wndy60xsajvloa4juu4z.jpg) <span> <br /><br />- </span>  don't forget to click **change all**       
-  we migrate Definitions and Data so both sides are selected       

5.          
Skipping special settings we use defaults we start the task in background             

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lsdydsj347hvc9upjjk3.jpg)<span> </span>    

6.       
Now we check the results and see everything was working without Errors     
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/49zx6zfeh5rd6yvcoc0d.jpg) <span>    </span>  
  
- You might see errors if tables depend on the content not yet migrated.         
- And wait for completions until the status shows **Done**     

7.       
We terminate the Migration Wizzard and return to normal table view filtered by **dc*     **
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/soz4lfx9gptcmgvi77xs.jpg) <span>     <br /><br />- All 8 tables are visible and show meaningful columns</span>  

8.               
Selecting a table and clicking on **OpenTable** shows reasonable contents     
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ono36cv066h2ag8pit85.jpg) <span>     <br />                         </span>  

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/88hl3cppnbvx2l0nslmf.jpg) <span> </span>       

9.      
A look into the related generated Class Definitions confirms the result and successful completion.      
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ic476gdki3i5meewuca9.jpg) <span> </span>