Full Onboarding Guide for Existing Systems

How To Generate the CSV Files

## required args:
    %OBEVO_HOME%\bin\deploy.bat DBREVENG -mode data -inputDir %s -outputDir %s -dbType %s -dbHost %s -dbPort %d -dbSchema %s -username %s -password %s

## optional args:
# 1) you can add this for DB2
    -dbServer <serverName> # for DB2 databases that require a server parameter
# 2) one of the following is required, but not both
    -tables table1,table2,table3,... # comma-separated list of tables to reverse-engineer
  or
    -inputDir dir # dir containing a file static-data-tables.txt that has the list of tables, one per line (you can use this in case you have many tables that are hard to pass in by command line)

# 3) If you want to automatically detect certain columns as "updateTime columns" and generate the CSV files as such, use this arg. The "updateTime column" concept is explained below
    -updateTimeColumns col1,col2

Clarification for certain params:

• -outputDir will have the CSV files written there
• -dbType should be SYBASE*IQ, SYBASE*ASE, DB2
• -updateTimeColumns:
  • Often times when we have static data tables, we’d denote a field like updateTime for audit purposes, i.e. to indicate when the row was added or inserted
  • But if we define the static data in your source code, then you can’t truly add the updateTime column at the same time, as that will get deployed later
  • Hence, we allow users to define certain columns as “updateTime columns”, such that the tool will automatically set this value to the current time whenever the row is inserted or updated
  • To do this, you would add the following metadata tag at the start of your CSV file: (the value is singular: only 1 column can be denoted as such)

//// METADATA updateTimeColumn=“col1” …

• In terms of the reverse-engineering:
  • The arg defined in the reverse engineering is to specify which columns you expect to be used as such, as teams may have certain conventions, e.g. timeUpdated, updTmstmp, …
  • The reveng arg is plural in case you happen to have multiple such columns

Generating the data as Insert statements

If you can’t or don’t want to maintain your static data as CSVs, and want to keep it as inserts, you can use Aqua to do this (i.e. Tools -> Export Data), or define these files manually, or whatever other method that you have

Explaining the -onboardingMode flag

As the deployment described here will be an iterative process to find and then resolve issues, it becomes important to easily identify exceptions. The onboarding mode helps via the following:

1) Any files that cause exceptions will get moved into either the /exceptions or /dependentOnExceptions folders, and will have a separate “.exceptions” file created containing the exceptions you have.

• /exceptions gets created for normal cases.
• /dependentOnExceptions is created if we detect that the exception was caused due to an earlier exception on another object (i.e. something already in /exceptions

2) Upon correction of SQL files and subsequent rerun, files will get moved back to the home folder

Onboarding workflow

Your workflow then specifically becomes the following:

• 1) Run a deployment using -onboardingMode with your reverse-engineered DDLs
• 2) Fix the issues you see in the objects stored in /exceptions, whether by fixing SQLs (see notes below), fixing the DB environment itself, or deleting the file and choosing not to maintain it.
• (leave the “dependentOnExceptions” alone for now)
• 3) Rerun the deployment w/ the -onboardingMode command. You should expect the /exceptions objects you just fixed to be successfully deployed. This means the objects in /dependentOnExceptions now have a chance to succeed. If they do, they will get moved to the regular folder. If not, they will get moved to the /exceptions folder
• 4) Go back to step 2 and repeat steps 2-4 until there are no more exceptions during your run
• 5) Do a final full-deploy (i.e. cleanEnvironment, followed by deploy) to help ensure that the SQLs are actually valid and compatible w/ all environments

Common problems encountered + resolution steps

• Objects may refer to other non-existent objects (e.g. views or stored procedures referring to tables that no longer exist). This can particularly happen for long-lived systems
  • A suggestion here is to do a grep on the log file of the deployment that you tried for the SQL error that indicates a reference to an object that doesn’t exist (e.g. for Sybase, the message is “nested exception is com.sybase.jdbc3.jdbc.SybSQLException: ArchivingCandidate not found. Specify owner.objectname or use sp*help to check whether the object exists (sp*help may produce lots of output)”. Hence, you can try grepping for “not found”
  • Once you get that list, send it out to your team (possibly w/ the object that referred to it) to see if these objects are actually needed. Likely, you should be able to delete these objects (definitely delete them from your source code, and drop them in production prior to onboarding)
• Views/stored procedures/triggers that are referring to objects in other schemas. Depending on your situation, you may either:
  • need to tokenize the schema name in case it is different on your dev env vs. production (e.g. account*master*dev vs. accountmaster)
  • see that the dependent object exists in prod but not dev. In that case, look to get that schema cleaned up (potentially via Obevo? You can do that as a separate project)
• Circular dependencies in stored procedures that the default algorithm could not detect (maybe due to comments in your files). In these cases, add the includeDependencies/excludeDependencies as needed (see earlier parts in this doc for reference)
• (For Sybase ASE) The stored procedure may have been relying on a pre-existing temp table (i.e. the client app creates the temp table, populates the data, then calls the SP that uses the temp table).
  • In this case, you must create the temp table before creating the SP. Though the temp table won’t exist outside the connection, the SP will remember the name and structure, and thus expect it to be created when called
  • To handle this case, just add the “create temp table” statement in your SP file prior to the “create procedure” statement, and then add a “drop temp table” statement after the actual SP sql (all within the same file)
    • See the example below for reference

create table #MyTemp (
    FieldA varchar(32),
    FieldB int
)
GO
create proc SpWithTemp (@MaxCount int) as
begin
    select * from #MyTemp

    // Do whatever processing you need
end
GO
drop table #MyTemp /* cleanup */
GO

• For the staticData loads, a couple common issues may arise:
  • If the table does not have a primary key, then the CSV mode will not work, as mentioned earlier in the docs. Either add the PK to the table, or switch to the delete/insert mode
  • If your data includes a backslash (i.e. ), then you need to convert it to escape it w/ another quote (i.e. \\ ). This is because of a bug w/ the underlying CSV reader implementation OpenCSV (which otherwise is truly an excellent library!)

If you run into any other kinds of problems that you cannot seem to debug, feel free to reach out to the Obevo team

Apply this to your other environments

h:\obevo\bin\deploy.bat DBDATACOMPARE -configFile H:\mydir\dbComparisonConfigExample.properties -outputDir H:\mycomparisons\

Cleanup old/unmaintained objects as you see fit

• Note - we ultimately are planning on a utility GITHUB#1 to cleanup any objects that are in the environment and not in the source code. Till then, you can try the following manual cleanup options.
• Removing backup tables from production (e.g. Transaction*bkup, Transaction*20130101)
  • The suggestion is that you do not include these tables into your code base - delete them before you do the INIT command (if you already did the INIT command, you can just run it again after your changes). Drop these manually from production (I’d do this manually as the tool currently does not support dropping tables - better safe than sorry from a tooling perspective! though we will eventually support this)
• Removing unused views/sps
  • In this case, I “would” keep these in your Obevo codebase, as after your init command, you can then delete these in a controlled manner. (I’m less worried about the risk of dropping stored procedures, as you can easily add them back)

See rest of recommended steps…

Follow the rest of the guidance on “More Recommended Steps” in Full Onboarding Guide for New Systems