Re: IBMi to AWS postgreSQL data warehouse

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Jay

Too much fun, eh?

Products such as SEQUEL have the ability to export data by various 
means, including FTP. They can also communicate with non-IBM i RDBMS 
directly using JDBC.

You might look at generating things like XML or JSON - IBM i SQL has 
several tools for that, and can be executed in RPG. There are also tools 
like CGIDEV2 and Scott Klement's JSON tooling, if you want to roll your 
own. I can even imagine writing an RPG-Open Access tool that could be 
used to "WRITE" to the ETL using native RPG opcodes.

HTH
Vern

On 10/24/2019 7:15 AM, Jay Vaughn wrote:
> So our non i folks decided to create a small 29 table DWH for a client.
> They know little about our tables.
> They used symetricDS to pull from the i and populate a staging database.
> An ETL tool then moves that to an AWS cloud PostgreSQL data warehouse.
>
> The symDS tool auto generates sql triggers that are slapped on our i
> tables.  A couple of tables were so large in row size (many columns), that
> the auto generated sql triggers were so inefficient that I had to replace
> them with an i DB2 trigger that literally hands off the before/after image
> to a batch process to carry out the insert of those images to a symDS table
> that is also on the i.  Apparently the symDS tool pulls changes form that
> symDS table on the i.
> Then, due to the fact that we have initialized blanks in some of our zoned
> fields that sql doesn't play well with, I also had to put our own trigger
> on that one... and there may be more eventually.
>
> I'm at the point where I'm convinced this is not the best game plan for the
> DWH.
>
> Has anyone got any suggestions for a better IBMi to AWS postgreSQL
> replication that would eliminate that symDS staging piece?  Preferably
> something that can connect directly to the AWS cloud from the i... even if
> custom code needs to be written?
>
> tia
>
> Jay