Archives of the TeradataForum

Message Posted: Wed, 27 Jul 2005 @ 16:27:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Bill,

If you're talking about backup of a table this size, then you'd need to cluster archive in all probability. Unless you've got a good BAR architecture with node-attached tapes, then the throughput will be too slow for a traditional all-amp arc.

Run your cluster archives as wide as you realistically can (i.e. multiple jobs in parallel), covering different groups of clusters in each one (don't forget they start at zero!). We've used this approach quite extensively, as we're a little constrained at this site on our BAR throughput rate, so we need to be a bit creative at times!!

- Restore to same system using different user IDs in parallel, loading to the same clusters as specified in your archives.

- Restore to different system via all-AMP restore (COPY) - if you're loading to the same tablename, and you're working on just the single table, then you're going to have blocking problems. Better to run multiple all-AMP COPY jobs via different user IDs to temp.table names in your target database, then run a single optimised insert-select to create the final table copy (n.b. you'll need to run BUILD jobs on these temp.tables once the restores complete, before running the ins-sel).

- If you're archiving/restoring whole databases via cluster process (i.e. not just the 1 table), then again you'll be hampered by locking on the restore side of things if you archive/restore at the *database* level. If you need to do this, then generate some dynamic SQL for your archive job to archive the database contents at the *table* level, then when you come to run your parallel restores, you'll be copying data (and locking) at table level also.

Journals are a different ballgame really.

I've been involved with journalling on a table c.0.7Tb, and it was successful ....but with some caveats.

i) Your target table this is updated via journal rollforward (usually your DR copy) can only really be updated by journals. Don't start adding in BTEQ deletes/inserts/updates, or you run the risk of screwing up the internal unique row ID for NUPI tables.

ii) There is (or was ....haven't checked this for quite a while now) an overhead with traditional BTEQ updates on tables that are also subject to delta capture via permanent journals - insert was okay, update was heavy, delete was awful. However, multiload delete avoided all of the problems with deletes - worked really well, with no degradation in performance.

iii) Don't rely upon journal compatibility between different Teradata systems, between releases (e.g. if you ever have a situation when your production setup is at V2R5, DR at R6). You generally can't rollforward a PJ from a previous release, into a more updated one. How much this is an issue for you, will be dependent upon how you plan to use your journals, i.e. roll them forward continually, or just keep them on standby, but you will have an issue when you come to upgrade your DBS.

So, I'd recommend their usage, but with the above thoughts in mind. They have a very specific usage, and I found that when they worked, they were great - however, when we came across bugs or problems, they were pretty cumbersome. Remember - this stuff is used pretty rarely so the functionality is not being hammered from all-sides by every Teradata site across the globe, and although Teradata obviously tests extensively, there's a greater chance of you coming across something unexpected with PJ's.

hope this helps,

cheers,

Mark

	<Prev	Next>		<<First	<Prev	Next>	Last>>