Data Refresh

[Anon Anon]

How do you all refresh data from one subsystem to another?

 

The way I do is, I've a list of tables to be refreshed in a table. I read that table, using a COBOL program and build Fast Load and Fast Unload JCLs  and then run all the unload jobs first and then run all the load jobs. Sounds simple, right?

 

Here are the problems I've.

 

1. I use "OUTPUT-FORMAT LOAD" in the load control when I'm unloading data. This means, the tables structure have to match exactly between source and target. Sometimes the table structures don't match (one subsystem might have an extra column). So I run a compare DDL to make sure the structures match. If not, I sync them up manually - this is a time consuming process. If I use OUTPUT-FORMAT DSNTIAUL, I don't have to do this, but it's too slow.

 

2. We're on r16 of CA for DB2 tools. And it doesn't support LOBs and Index on Expressions. We're upgrading to r18. Does anyone remember on top of their head, if r18 support these tables? Without support for these objects, I'm using IBM tools just to refresh these tables. For Index on expression, I'm dropping those indexes, load the table and recreating the indexes - again, time consuming process.

[Chris_Stallone]

Is anyone able to assist further with this question?

Thank you!

[Anon Anon]

Not yet

[Anon Anon]

Sorry it took so long for this answer, but I needed to confirm a few things first. Here goes:

 

1. If you already know what your target table definition looks like, you can certainly SELECT by column name, and add literals to the mix if a constant value is all you need in the ‘extra’ columns. You can use Output-Format DSNTIAUL, VARIABLE, or FIXED with an INTO column list with any needed column specifications for output format conversion. Then you would create the Load Control for that (target load) with PFU, and LOAD it with PFL using the control cads from the UNLOAD. But, if you don't know what your target table looks like, you can do a  SELECT from the Tables in question with DDL-CONTROL INTABLE,ONLY and compare the DDL to find out if there are definition differences to start off, You will not be able to do the SELECT cols / literal list INTO cols list FROM crt.tbl and use OUTPUT-FORMAT LOAD, as INTO is not supported. In the end, yes, O-F LOAD is a bit faster than the other Output-Formats, and it is even faster if you have to do INTO conversions for missing / extra columns, but the other Output-Formats will get the job done.

 

2.R18 in Fast Load does support LOBs and Index on Expression. The only caveat is that Index on Expression currently only supports the SUBSTR and SUBSTRING SQL functions.

 

 

Hope that helped.

[Anon Anon]

Thanks for the info about r18's support for Index on Expression. I'm saddened to know though that, it still doesn't support all Index on Expressions. We've quite a few UPPER functions

 

About O-F LOAD vs other formats, the time difference (both CPU and elapsed) is significant. For little over 1,000 tables of variable sizes, we see a difference of about 20%.

[Anon Anon]

Thanks for the additional information. We were not able to get more SQL functions into Index on Expression when we developed our support. At the time, I was trying to figure out what functions would bring the most value to our customers. Knowing that you use UPPER helps me plan for additional support in the future. We were also looking at CAST for LOBs and CONCAT.

 

For O-F LOAD, our in-house testing don't show us that kind of time difference, but every system is different. Thank you for pointing out the variance in you system.

[Anon Anon]

In case it helps for future planning ... here are the other functions we use in Index on Expressions: DATE, YEAR, || (concat), RTRIM, STRIP.

 

UPPER is the frequently used function though.

[Anon Anon]

This definitely helps. Know what functions are actually used means we won't waste time on those that are not used. Thanks again.

[Andrew Badgley]

In addition to John's post regarding our CA Fast Unload & CA Fast Load offering, CA RC/Migrator or CA RC/Compare is another option to keep your objects in sync.  Use either of these products to keep your objects in sync as an automated process and then your "OUTPUT-FORMAT LOAD" process would work without hicups.

[Anon Anon]

That's a good option, but unfortunately we cannot do it. The reason is, there are table changes that are in dev/test/qa environments that haven't made to prod, because all the application components are production not ready yet. And we refresh data once a quarter.

[Jan Marek]

You may also use a bit different concept than UNLOAD/LOAD the data to 'refresh' your clone. You can use CA Log Analyzer to scan the log for all changes done on all your tables and produce a data file in a LOAD-like format on one subsystem. Than you can use CA Log Analyzer APPLY feature to redo the same updates on another subsystem. This does not resolve all table structure changes, but may some. You can also control the SQL codes processing of the APPLY process.