DB2 for LUW SQL PL has developed a long way and now offers a rich programming environment. I will describe how I have used the newer features of SQL PL to deliver massive performance and availability improvements with federated data synchronization in a real time transactional environment over other available techniques that were in use. This has been delivered via a generic stored procedure that can synchronise any pair of tables very efficiently and is currently used in a production environment. This has delivered significant cost savings for an organisation through •eliminating outages for synchronisation•eliminating many scripts•minimising lots of expensive HADR logging and CPU•in some cases, eliminated the need for bespoke programming/scripting to apply delta data to tables•the stored procedure, used with DB2 Views, was used to implement simple ETL tasks in an efficient mannerMy presentation will use some real DB2 for LUW SQL PL code to demonstrate some of the features and topics such as:•FOR loops on cursors•Packing and unpacking ARRAYs – Cursors, UNNEST, CARDINALITY and TYPE•Generating and executing SQL PL dynamically•HADR considerations for design of update processes•MERGE considerations•Use in a Federated environment – what to do with target tables that are NICKNAMEs•Debug techniques for dynamically generated SQL and SQL PL code•Concurrency and locking•Tips and pitfalls to avoid•Briefly look at differences between DB2 for LUW and DB2 for z/OS syntax•Use of the Oracle PL SQL syntax and why I’m not using that!You’ll get a feel for what can now be achieved in DB2 SQL PL using the newer features now available and perhaps come away with some useful code you could use in your environment straight away!
UPS6756, #1544403 Oleander Drive, Suite CWilmington, NC 28403 Phone: (910) 660-8649Fax: (910) 523-5504