How to know when an OnLine table maintenance has finished on ADS12 using ARC?

[Former Member]

Hi all,

I have seen that I'm able to pack, reindex and reestructure tables in OnLine mode, using the ARC (Advantage Data Architect) with that new ADS12, while the users are using them.

I have tried using pack and seems it works, but after while, I have tried to add a field using OnLine reestructuring and I have received a warning message "Error 7200; NativeError=7175: An online pack operation is already active on the table".

How can I see which online operations are active for a table or for a server?

Are there some utility to know that?

Thanks

Eugeni

[chrisfranz · Accepted solution]

Eugeni,

 
In order for an online pack operation to complete all users must close the table.  All of our online table maintenance functions have associated events that you can receive. The event for online pack is OPFinalStage_<tablename>. This event is triggered when the pack operation is completed.

  To listen for an event you must do so on a separate thread. First you register for the event then you wait for the event to be triggered. To do this in ARC simply open a separate query window.

  You can view a version 11 demonstration here the mechanism is the same with version 12.

Chris

[Former Member · Accepted solution]

Hey Chris,

thank you very much for your help!!

The screencast is very clear and asnwer my question.

But after showed it I have a new question.

In the screencast, ARC11 doesn't have the Online Pack option in the menu, but now ARC12 has it.

Then, if I use that Online Pack option menu, how can I see when has finished?

I try creating the events you show in the screencast but it not seems to work.

I hoped to have an option to show which online actions are in process in the server or in some dictionary or table.

Cheers

Eugeni

[chrisfranz · Accepted solution]

Eugeni,

  Let's say you want to pack a table called Customers that is currently in use. To do this in ARC you would open two SQL windows. In the first window you would setup an event listener by running the following SQL Statements.

EXECUTE PROCEDURE sp_CreateEvent( '__OPFinalStage_Customer', 0 );

EXECUTE PROCEDURE sp_WaitForEvent( '__OPFinalStage_Customer', -1, 0, 0 );

  These queries will continue to run in the window until the event is sent.

You are now ready to pack the table using the Online Pack function. Open a second SQL window and run the following SQL Statement.

EXECUTE PROCEDURE sp_PackTableOnline( 'Customer' );

  As soon as the procedure is finished you should see a result in the first SQL window. You can do this same thing in your application by creating a second thread that will register and wait for an event. When the event fires you could notify your client to restart the application.

  There is no generic function to see all of the online operations, however, you can run the sp_GetSQLStatements procedure to get a list of running queries. You could search this result for any of the Online system procedures (i.e. sp_PackTableOnline, sp_ReindexOnline). The query would look like this.

SELECT * FROM (EXECUTE PROCEDURE sp_GetSQLStatements()) RunningQueries

  WHERE CONTAINS('*online', Query)

  Hopefully this clears up the process.

Chris