Database migration

Database migration is generally a complex task that can involve not only transferring data between storage types and formats from one database server to another, but also rewriting SQL statements or even business logic stored procedures (SP).

In comparison with standard hand made migration scheme, we offer a powerful set of tools developed and proven in complex relational database models. Depending on the nature of your system, these tools and our consulting specialists can ensure that your company’s database transition is smoothly accomplished.

From experience, we are familiar with the complexity and cost involved in a large database migration and the problems that arise in this process when inappropriate methods are employed. This is why we always check with a potential customer that the use of our tools and methods can offer a significant advantage.

Migration tools

In comparison with standard migration consultancy, which offers little more than database support, we can point to considerable experience in writing large enterprise applications in cross and native database syntax. Furthermore, we will teach your team a methodology and provide them with a powerful set of tools to reduce costs and optimize the migration process.

These tools include:

  • Multi database copy tool with automatic conversion from data types (including geometric data types)
  • Multi database schema check
  • SQL XML grammar
  • DDL XML grammar
  • DML XML grammar
  • SPL XML grammar
  • Triggers XML grammar
  • Support for conversion of geometric data types

Multi database copy

The copy tool is able to replicate all data from a target database to a destination, independently of the engine, creating tables, indexes, constraints and mapping data types when engines differ. With little effort, and after the time involved in copying you can see and explore your data in the new database. Of course, migration is not performed.

  • Generates table structures according to target data types
  • Automatically disables triggers and sequences during the copy process
  • Automatically sets up sequence after a table copy
  • Supports cross database rowid generation
  • Supports geometric data type conversion allowing easy migration of spatial engines
  • Supports after copy index and foreign key construction
  • Supports after copy trigger and SPL compilation

Multi database schema check

Once you start a migration, you can generate an XML schema from your original database. This will let you translate the database model to any supported engine.

But what if your system continues to operate and even undergoes structural changes during the migration process? The schema check even compares databases of different types and shows differences in table structures, primary keys, foreign keys, indexes and constraints. A comparison can also be made with the master XML schema model. In both cases, a proposal for the changes to be applied to ensure that databases have the same physical structure is shown.

Support for development, test, pre-production and production systems

The migration tools are built around a database dictionary. This dictionary allows programmers to store their code (DML statements, SQL queries, SPL code, initial table data, etc.), which will constitute the applications database. Once stored in the dictionary, a set of web or command line commands enable the compilation, checking or release of upgrades to a database or a group of databases.

XML-XSQL syntax

The DDL, DML, SPL, trigger translation engine provides a framework with a common XML syntax in which developers can write applications in a database-independent syntax.

XML-XSQL syntax available

DDL

The database copy process can automatically create an XML model to generate the Data Definition Language of the database. You can see all your tables and objects defined in a natural XML definition that will allow on-line translation to the target database.

DDL – XML transformation sample

DML

An XML grammar allows database-independent SQL statements to be written.

DML – XML transformation sample

Stored procedures

Business logic written in stored procedures, functions or triggers must be manually rewritten in XML. We offer this service ourselves or we can teach you how to do it. Thereafter, you will be able to automatically translate this logic to the target engine.

This step has a major advantage over conventional manual coding, since the AX XSQL translation engine will validate and generate the appropriate code without human errors.

The stored procedure manager will also take care of compilation status in target databases (development, test and production).

SPL – XML transformation sample

Triggers

If you have triggers, you are probably familiar with the complexity and differences involved in writing database-independent triggers. As in the case of stored procedures, you can use an XML grammar and the translation engine will generate the appropriate triggers for the target database.

Trigger – XML transformation sample


Geometric data types

When a database has geometric data types, this constitutes a special case. We offer transportability between Oracle Spatial, DB2 Spatial Extender, Informix Spatial DataBlade and Postgres PostGis. The DML grammar offers a full set of functions to write SQL-independent queries and the DB copy engine will transfer data securely.

<select>
    <columns>
        d.the_geom,
        d.id,
        d.p1,
        d.p2,
        d.p3,
        d.p4,
        d.p5,
        d.p6
    </columns>
    <from alias='d' >
        <select>
            <columns>
                bican_zonas_censales_data.p1,
                bican_zonas_censales_data.p2,
                bican_zonas_censales_data.p3,
                bican_zonas_censales_data.p4,
                bican_zonas_censales_data.p5,
                bican_zonas_censales_data.p6,
                bican_zonas_censales_data.id,
                <geo.asWkt>bican_zonas_censales.the_geom</geo.asWkt> the_geom
            </columns>
            <from alias='c' >
                <select>
                    <columns>
                       <geo.buffer>
                            <geom1><geo.aggrUnion pg_collect='y'>bican_areas_influencia.the_geom</geo.aggrUnion></geom1>
                            <dist>0</dist>
                       </geo.buffer> the_geom
                    </columns>
                    <from table='bican_areas_influencia' />
                    <where>
                        bican_areas_influencia.id_oficina = '#oficina'
                    </where>
                </select>
                <join table='bican_zonas_censales'>
                    <on>
                        <geo.intersects>
                            <geom1>c.the_geom</geom1>
                            <geom2>bican_zonas_censales.the_geom</geom2>
                       </geo.intersects>
                    </on>
                </join>
                <join table='bican_zonas_censales_data'>
                    <on>bican_zonas_censales.gid = bican_zonas_censales_data.id</on>
                </join>
            </from>
        </select>
    </from>
</select>
-- ************************************************************************************
-- DEISTER WebStudio XSQL-SELECT Tue Oct 11 11:21:50 CEST 2011 Engine: ids
-- ************************************************************************************
SELECT
        d.the_geom,
        d.id,
        d.p1,
        d.p2,
        d.p3,
        d.p4,
        d.p5,
        d.p6                  

  FROM (SELECT
                bican_zonas_censales_data.p1,
                bican_zonas_censales_data.p2,
                bican_zonas_censales_data.p3,
                bican_zonas_censales_data.p4,
                bican_zonas_censales_data.p5,
                bican_zonas_censales_data.p6,
                bican_zonas_censales_data.id,
                ST_AsText(bican_zonas_censales.the_geom) the_geom     

  FROM (SELECT
                       ST_Buffer(SE_Dissolve(bican_areas_influencia.the_geom),0) the_geom            

  FROM bican_areas_influencia
 WHERE 

                        bican_areas_influencia.id_oficina = '#oficina' 

) c
      ,bican_zonas_censales
      ,bican_zonas_censales_data
 WHERE 

                        ST_Intersects(c.the_geom,bican_zonas_censales.the_geom)
                     AND
       bican_zonas_censales.gid = bican_zonas_censales_data.id

) d
 WHERE 

                        ST_Intersects(c.the_geom,bican_zonas_censales.the_geom)
                     AND
       bican_zonas_censales.gid = bican_zonas_censales_data.id
-- ************************************************************************************
-- DEISTER WebStudio XSQL-SELECT Tue Oct 11 11:21:50 CEST 2011 Engine: oracle
-- ************************************************************************************
SELECT 
        d.the_geom,
        d.id,
        d.p1,
        d.p2,
        d.p3,
        d.p4,
        d.p5,
        d.p6                  
    
  FROM (SELECT 
                bican_zonas_censales_data.p1,
                bican_zonas_censales_data.p2,
                bican_zonas_censales_data.p3,
                bican_zonas_censales_data.p4,
                bican_zonas_censales_data.p5,
                bican_zonas_censales_data.p6,
                bican_zonas_censales_data.id,
                SDO_UTIL.TO_WKTGEOMETRY(bican_zonas_censales.the_geom) the_geom     
            
  FROM (SELECT 
                       SDO_GEOM.SDO_BUFFER(SDO_AGGR_UNION(SDOAGGRTYPE(bican_areas_influencia.the_geom,0.05)),0,0.05) the_geom            
                    
  FROM bican_areas_influencia
 WHERE 

                        bican_areas_influencia.id_oficina = '#oficina' 
                    
) c
      ,bican_zonas_censales
      ,bican_zonas_censales_data
 WHERE 
       
                        SDO_ANYINTERACT(c.the_geom,bican_zonas_censales.the_geom)
                     AND 
       bican_zonas_censales.gid = bican_zonas_censales_data.id

) d
 WHERE 
       
                        SDO_ANYINTERACT(c.the_geom,bican_zonas_censales.the_geom)
                     AND 
       bican_zonas_censales.gid = bican_zonas_censales_data.id
-- ************************************************************************************
-- DEISTER WebStudio XSQL-SELECT Tue Oct 11 11:21:50 CEST 2011 Engine: postgres
-- ************************************************************************************
SELECT
        d.the_geom,
        d.id,
        d.p1,
        d.p2,
        d.p3,
        d.p4,
        d.p5,
        d.p6                  

  FROM (SELECT              
                bican_zonas_censales_data.p1,
                bican_zonas_censales_data.p2,
                bican_zonas_censales_data.p3,
                bican_zonas_censales_data.p4,
                bican_zonas_censales_data.p5,
                bican_zonas_censales_data.p6,
                bican_zonas_censales_data.id,
                ST_AsText(bican_zonas_censales.the_geom) the_geom     

  FROM (SELECT
                       ST_Buffer(ST_Collect(bican_areas_influencia.the_geom),0) the_geom            

  FROM bican_areas_influencia
 WHERE 

                        bican_areas_influencia.id_oficina = '#oficina' 

) c
       INNER JOIN bican_zonas_censales ON
                        ST_Intersects(c.the_geom,bican_zonas_censales.the_geom)

       INNER JOIN bican_zonas_censales_data ON bican_zonas_censales.gid = bican_zonas_censales_data.id

) d
-- ************************************************************************************
-- DEISTER WebStudio XSQL-SELECT Tue Oct 11 11:21:50 CEST 2011 Engine: sqlserver
-- ************************************************************************************
SELECT 
        d.the_geom,
        d.id,
        d.p1,
        d.p2,
        d.p3,
        d.p4,
        d.p5,
        d.p6                  
    
  FROM (SELECT                 
                bican_zonas_censales_data.p1,
                bican_zonas_censales_data.p2,
                bican_zonas_censales_data.p3,
                bican_zonas_censales_data.p4,
                bican_zonas_censales_data.p5,
                bican_zonas_censales_data.p6,
                bican_zonas_censales_data.id,
                bican_zonas_censales.the_geom.STAsText() the_geom     
            
  FROM (SELECT 
                       -- Tag [geo.aggrUnion] not defined for engine sqlserver.BufferWithTolerance(0,,) the_geom            
                    
  FROM bican_areas_influencia
 WHERE 

                        bican_areas_influencia.id_oficina = '#oficina' 
                    
) c
       INNER JOIN bican_zonas_censales ON 
                        c.the_geom.STIntersects(bican_zonas_censales.the_geom)
                    
       INNER JOIN bican_zonas_censales_data ON bican_zonas_censales.gid = bican_zonas_censales_data.id

) d 

Below is a list of the geometric functions supported that are available in the XML grammar:

Informix Datablade Postgres PostGis Oracle Spatial SQLServer Spatial DB2 Spatial Extender
ST_Point
ST_Line
St_Polygon
getType
getX
getY
getZ
getEnvelope
isValid
getCoordDim
getDim
getSRID
getNumVertices
asKml
asGml
asWkt
asWkb
asSvg
transform
reverseLine
distanceCartesian
distanceSphere
distanceSpheroid
length
lengthSpheroid
perimeter
area
centroid
withinDist
equals
touches
overlaps
covers
coveredBy
contains
within
intersects
disjoint
intersection
buffer
simplify
convexHull
difference
symDifference
aggrUnion
aggrMbr
aggrConvexHull