Migrate the System log to another system like Terradata.

[pankaj.puranik]

We have this requirement to move the System logs to Terradata.
The reason this is being done is because these logs are consuming a lot of space and we do not have the option of moving them to TSM.

So we need to migrate the metadata and documents (message 87 stores a document with the load log).
Any pointers on how we should do the extraction of metadata and documents and then correspondingly link them.

[Justin Derrick]

I've never heard a requirement like this before.

Consider purchasing the Storage Optimization feature for DB2 from IBM -- it compresses the database data by more than 50% in sites where I've seen it used.

There really isn't a great way to extract both the table data and the log messages.  I suppose you could slurp out an entire System Log table with arsdoc, but it wouldn't be in a format you could store easily in a datawarehouse.

-JD.

[Alessandro Perucchi]

Hello Pankaj,

if the System Log is taking too much space... then why not reduce the number of log that will get into it?
You can decide what kind of logs will be archive in "System Log". That way, you can reduce the space used.

Alex

[pankaj.puranik]

Agreed Alessandro.
But my concern now is getting rid of the already existing logs.

[Justin Derrick]

Agreed Alessandro.
But my concern now is getting rid of the already existing logs.

Or you could just reduce the Life Of Data And Indexes number, and CMOD will take care of it at the next expiration run, eliminating old tables that are no longer needed.

-JD.

[pankaj.puranik]

Agreed JD. But the management is reluctant in getting rid of the old data.

[Justin Derrick]

Heh.  They don't make it easy, eh? 

I'll re-iterate my suggestion of the Storage Optimization feature.  It's made a big difference at the customer sites where I've installed it.

-JD.

[Alessandro Perucchi]

What you can do is the following:

1) Extract data with documents behind:

Code: [Select]

arsdoc get -h <cmod> -u <user> -p <pwd> -G "System Log" -v -gcN -o export-SL-with-Document -i "where APPL_ID='T'"

2) Extract the databases values in a loose CSV format (with semi-colon instead of coma)

Code: [Select]

arsdoc query -h <cmod> -u <user> -p <pwd> -G "System Log" -v -e ';' -H -i "where APPL_ID<>'T'"


Then, you need to write a parser to integrate both extraction in your DWH solution... terradata or something else.

I hope that helps a little bit on how you can achieve what you want...

[Justin Derrick]

I'll just mention that without specifying the date with the -S option, this query/retrieve will take a very long time if your system has so many System Log tables that you're considering archiving it elsewhere. 

Otherwise, Alessandro is correct again.

The only thing we haven't addressed is how to remove the System Log data from CMOD. 

You can either reduce the 'life of data and indexes' value, or run arsmaint with the (rather dangerous) -t option to get CMOD to expire the data, but as if arsmaint was being run at a future date.

-JD.