How to delete or to truncate the Gdmart table using ETL

[gsarwono]

Dear Genesys expert,

I am new beginner for genesys system. Could you give me advise to solve the problem I have? The company I work has a lot of report using CCA, ETL, Gdmart and Brio Report. Up to now there are almost 4000 tables has been created in Gdmart database (or 250 GB in disk space), which related with the customer report (daily, weekly, monthly report). For the new customer, I have concern the number of tables we have and also the database space. Therefore I am planning to free up the database with deleting the table that no longer require, or truncate the table it. (Table/report that customer has left). I want to get you advise with this plan is there any procedure to delete the table that does not required any longer in Gdmart?
Can I delete the table directly from Gdmart database? If it is not possible then is there a way to use ETL or other Genesys tools to delete or to truncate the table in Gdmart, what is the command to do that?
And do you have documentation that I refer to?

Thanks for your help.

Rgds,

Gito

[catanirex]

I giess you mean the DataMart database?
The easiest way to free db space is to configure the ETL_Purge process to run every night. Do that in CME under the ETL_Service/ETL_Runtime application. Verify that purge.properties is configured correctly.
The you start ETL Assistant and configure how long you want to keep 15-minute (No AGG) data, hourly data, daily data etc.
This is described in 72rt_us_etlruntime.pdf, ETL Runtime Users Guide.

Do not delete any tables manually...

Good Luck!

[Adam G.]

The problem is that ETL_Purge can take a long time especially if there are GBs of data to get rid of.

This is what I have done manually in the past to get over this:

NOTE: ONLY DO THIS ONE TABLE AT A TIME AS THE PROCESS CAN TAKE A NUMBER OF HOURS!!

1, Get a list of how big each table is - the un-agg data ones are the biggest. I wrote a Brio report to simplify this process.
2, Stop the ETL runtime.
3, Decide how long you want to keep the data for in each table, ie, delete anything older than a month (if un-agg data).
4, Copy the last month's data to a new table
5, Drop the old table.
6, Rename the new table to what the deleted table was previously called.
7, Rebuild the Index against the new table
8, Run a standard Brio report against the new table to check there are no reporting issues.
9, Check the the size of the DB and tables - the affected table will be severly reduced
10, Restart ETL
11, Check ETL logs for errors (shouldn't be any)
12, Repeat as necessary

When the data has been manually sufficiently reduced, switch on the ETL_Purge.

If you've any questions, login on this forum and you'll see my email address.

Pavel

[catanirex]

Hi,

My experience of ETL Purge 7.0/7.1 on both MS SQL 2000 and Oracle 9.2 is that it is quite quick!
We ran it on a 4 year DataMart that had never been purged, and reducec the size with several GB within a couple of hours.

So my recommendation is still to try to use the supported tools as a first choice 

[g]

Thanks Catanirex and Pavel,

I am using Oracle 9i as Database, ETL service 7.0.1, DMA 7.0, CCA_Datasource_Tel 6.1. I will try using the tools first and see how is going. Btw I run from the database, and found number of table is 3693 tables, the biggest table is Call_trace (4.198 GB), another 45 tables about 1 GB, and the rest table about 0.5 GB

Cantenirex,

How you maintain the number of tables? Do you keep all the table in the database? or using ETL Purge it would authomatically delete the table that has not been use?

Thanks,

Gsarwono

[catanirex]

Hi,

You wrote "the biggest table is Call_trace "

Are we talking about Genesys DataNart database? There no such table in a standard db...

[Haldane]

G,
    Are you sure your not counting tables from within another schema. Login to the database using the the Datamart user or a user that can only see that schema and run the count agin.