Hello friends,

Another challange : Currently writing generic PruneJob Stored procedure.
This SP will do table prunning. Also it will, in some cases move rows to history table.

Beacuse I use CURSOR for deleting rows, I need to use CURSOR for moving rows to history table.

How can I get following functionality ?

I mean while iterating row by row I want to insert this row in history table and then delete current row as

DELETE FROM TRANS WHERE CURRENT OF <cursor>

Please note that this is generic SP that will be used for various tables with different structures.

Regards

DBFinder

declare c1 cursor for select * from .... ;

load from c1 of cursor ..... ;

followed by to purge

IMPORT from /dev/null .... ;

Great my friend Cougar8000,

Many thanks

DBFinder

Cougar8000,

Can do that Row-by-Row some how ?

I mean our SP being written is commit count based that counts rows and reports back. User choses commit count in SP parameters.

I hope you got my idea. If I do the entire RECORDSET for import from null ,may be transaction logs full error or locks for extended time.

That is why we use the SP.


Regards

DBFinder

I might be misunderstanding what you are trying to do here. maybe I need more clarification.

If you are able to identify records that need to be moved in your where clause then use that in the cursor and move that data.

If you are trying to purge just some records then IMPORT from /dev/null not going to work. Sorry if I might have misunderstood your request originally.

Yes, you would have to use DELETE and that WHERE clause. But do not let users pick COMMIT count. You as a DBA should set what that count is. I would think. Other wise they might over or under commit.

Users are all DBAs. One of our team takes care of prune jobs.

Here is what I am trying to do.

1.Declare a cursor for rows older than 36 days
2. delete rows one by one till end.
3. also while iterating count them so you can commit after a number.
4. close the cursor.

To modify this SP to support HISTORY table.

after step 1 but before 2

1.5 insert current row in History table.


This is because some tables are simply purged but some are moved to history tables.

This is in busy production databases, so we use commit count to relieve transanction logs.

I hope this explains everything.

However this is helpful too. In some other situation. thanks .


regrds

If you are using DELETE to remove older data from the tables, I assume you do not have deletes in you application.

Can't you create a DELETE trigger on the tables that need history and use a generic stored procedure to delete the records ?

You may also use :

Yes, you are absolutely right.

there are few that are setup like this. There are at least two tables that won't be able to move a huge data to their decendents. too busy.

Moreover setting these indiviually violates term generic. Demand is "Create a generic prune SP".

regards

Does LOAD FROM ct OF CURSOR work inside SP.

I'm having problem.

regards

ADMIN_CMD procedure

Well, let's not get into what 'generic' means

If the tables are huge, you initial proposal of deleteing row by row in a cursor and then inserting it will be a performance killer and so will be my trigger 'idea'

I think it may be a good idea to do two stored procs :
a) Copy data older than n days (or whatever your criteria is) from the main table to its history using load from cursor
b) Take the table, delete criteria and commitcount as input . if the table in question has 'history data' requirement call the procedure in a. Then go ahead and delete the data ...

For deleting the data, you can use the example I have given in my previous post. Delete with Fetch first n rows only in the subquery is easier to code and maintain than using the cursor and deleting it.

In the above steps, the history table will contain all data, even if the data is not deleted. Therefore logic for selecting data to load into the table needs to handle this. But the advantage is that you copy the data in one step and therefore have lesser impact on performance.

HTH

Sathyaram

Good,

I will test this technique and hope this will be much better than the one we are using currently.

Thanks for your help.

Regards

DBFinder

Hi,

Can anyone help me find the mistake here.

Error:

If I change V_SQL as

Then it works

Any help on this ??

Regards

If you look at the syntax for using LOAD with ADMIN_CMD here:

LOAD using ADMIN_CMD - IBM DB2 9.7 for Linux, UNIX, and Windows

You will see that your second example (that works) is the correct syntax. Evidently, you can't reference a previously Declared Cursor like you can in a normal Load. You 'define' the cursor by putting the SQL statement in the Load statement.

Here is an example:

LOAD FROM (SELECT * FROM T1) OF CURSOR INSERT INTO T2

Thanks,

You are right, sorry that I missed it while reading.

Regards