Unanswered: UDB Live-Shadow tables and usage within SPs
I have an application running against a UDB 9.7 Stored Procedure (SP) at around 300 TPS.
I get incoming data feeds at various frequencies (daily, weekly, monthly) which are handled by Delta loads (100k records) or LOAD REPLACE for the weekly and monthly complete files (40MM records).
- The Delta loads take place off-peak daily (via a MERGE)
- I use a live-shadow concept for LOAD REPLACE (ie loading tableA_v1 whilst SP hits tableA_v2).
The 'which table is live?' question is handled by a control_table which gives a 1 or 2 value. This is then used to determine which cursor to call in the SP. Something like this pseudo-code:
cursor1: select 1 from tableA_v1
cursor2: select 1 from tableA_v2
select active_id into v_active_id from control_table
if v_active_id = 1 then open cursor1 else open cursor2
This all works fine until reality hits:
#1: I have 4 tables (tableA, tableB, tableC, tableD) each loading and flipping at different times. Given I need every combination I'll be copying and creating 4 * 4 = 16 cursors!
#2: I'm using pureXML (incl xmlTable) queries which, although very efficient, are very heavy in terms of SQL and therefore the SPs are very very large (almost 4mb) and are becoming very unmanageable.
I've tried attach/detach partitions but get locking issues causing app-thread build-up issues.
I've tried dynamic SQL (ie determine the table values required at real-time, and append to a string which contains the SELECT clause; then PREPARE and OPEN it) but since it needs to create and compile it's way slower
This can't be a unique problem, so any ideas on how to simplify this whole thing (ie combine LOAD REPLACE and MERGE approaches with 4 tables in a SQL JOIN) from the gurus would be most appreciated.