Database Migration

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

Compared to standard manual migration paradigms, we offer a powerful toolset proven effective 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 goes smoothly.

Experience has made us familiar with the complexity and cost involved in a large database migration, as well as the problems that arise when inappropriate methods are employed. That’s why we always check with a potential customer that the use of our tools and methods can offer them a significant advantage.

Migration tools

Compared to standard migration consulting, which offers little more than database support, we can point to our considerable experience in writing large business applications in both native and cross-database syntax. Furthermore, we will train your team and provide them with a powerful set of tools to reduce cost and optimize the migration process.

These tools include:

  • Multi-database copying tools with automatic data type conversion (including geometric data types)
  • Multi-database schema check
  • SQL XML grammar
  • DDL XML grammar
  • DML XML grammar
  • SPL XML grammar
  • XML trigger grammar
  • Support for conversion of geometric data types

Multi-database copy

The copy tool replicates all data from a target database to a destination, independently of the engine, creating tables, indexes, constraints and mapping data types where engines differ. After the copying process is complete, you can effortlessly see and explore your data in the new database. Migration is not performed in these cases.

  • Generates table structures according to target data types
  • Automatically disables triggers and sequences during the copy process
  • Automatically installs the associated sequence after copying a table
  • Supports cross-database rowid generation
  • Supports geometric data type conversion, allowing for easy migration of spatial engines
  • Supports construction of post-copy indexes and foreign keys
  • Supports compilation of post-copy triggers and SPL

Multi-database schema check

Once migration has started, 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 keeps running and even undergoes structural changes during the migration process? The schema check compares databases of different types and reveals 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 of change will appear to ensure that the same physical structure is maintained.

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

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 instructions enable compilation, checks and/or update releases for a database or 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 online 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, and are also willing to teach the client how to perform 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, testing and production).

SPL-XML transformation sample

Triggers

If you have triggers, you probably are familiar with the complex 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 your target database.

Trigger-XML transformation sample


Geometric data types

When a database has geometric data types, it constitutes a special case. We offer transportability between Oracle Spatial, DB2 Spatial Extender, Informix Spatial DataBlade and Postgres PostGis. 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 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