In our client/server application there are 2 definitional databases that are copied down when the specific network trigger file does not match the local one. This process allows workstations to process transactions in CAPTURE mode when the network is down which has been proved very helpful in a number of instances.

This process has been in production in SA 5.5 for years and now, after the application was upgraded to SA 12.0.1, we have also implemented Maintenance Plans on all databases running in production, release, and development environments.

When the application is installed, customers were instructed to use the Maintenance Plan Wizard and have all the parameters setup for backup and e-mail notification on the server which are working fine.
The issue we are now facing is that when a workstation is left ON after hours the maintenance plan does generate an error 08W10 since the directory structure defined for the backup file on the server does not have a match on the workstation.

An e-mail notification with a FAILED status is sent to the Recipients with a message similar to the one below:

Maintenance plan BACKUP for abc on abc started on 2012-09-10 at 14:41:04.300
Disabling new connections started on 2012-09-10 at 14:41:04.300
Disabling new connections finished on 2012-09-10 at 14:41:04.331
Disconnecting all users started on 2012-09-10 at 14:41:04.331
Disconnecting all users finished on 2012-09-10 at 14:41:04.331
Validation started on 2012-09-10 at 14:41:04.331
Validating database pages
Validation finished on 2012-09-10 at 14:41:04.862
Backup started on 2012-09-10 at 14:41:04.862
Backing up to archive: 'C:\APPS\release\Database\backup\abc.db'
The maintenance plan has ended because of the following error:
Specified database not found
SQLSTATE: 08W10
SQLCODE:  -83
Re-enabling new connections started on 2012-09-10 at 14:41:04.941
Re-enabling new connections finished on 2012-09-10 at 14:41:04.941
Maintenance plan BACKUP for abc on abc finished on 2012-09-10 at 14:41:04.941

Is there a way to force a maintenance plan to only start when the database is running on a database server?

OR is there a way to add the IP address of the sender in the email notification itself?

asked 10 Sep '12, 17:12

Derli%20Marcochi's gravatar image

Derli Marcochi
1.6k323877
accept rate: 33%

edited 11 Sep '12, 09:53


If I understand your request right, then the maitenance plan (MP) should only be started when the database is running on a databaser server (dbsrv12) and not when running on a personal engine (dbeng12)?

I don't think this is configurable via Sybase Central, but as the MP facility creates events in the database, you could adapt the according handler to add have it run only on database servers, something like

CREATE EVENT ... -- Note: Usually <MP name>_event
...
HANDLER
BEGIN
  IF (SELECT PROPERTY('IsNetworkServer')) = 'Yes' THEN
  ... original handler code
  END IF;
END;

In a similar fashion, you might be able to modify the email notification text to add the server name or IP address.

permanent link

answered 11 Sep '12, 03:24

Volker%20Barth's gravatar image

Volker Barth
31.5k321462677
accept rate: 32%

Another approach would be a DatabaseStart event that disables all events when running in an "undesired situation", such as a personal engine instead of a network server, in a test environment, whatever.

That can be done with several ALTER EVENT ... DISABLE or ALTER EVENT ... DROP SCHEDULE statements.

We use something like that to prevent backups from test databases (as copies of production databases) which would otherwise backup to an unwanted network share - hopefully not the "real production backup folder":)

(11 Sep '12, 06:00) Volker Barth

You are correct. I am researching a solution that could be implemented by the users via the wizard so the maintenance plan will only start when launched from a db server. However, based on your suggestion I think the question could be retagged as a product suggestion.?
Regarding the second part, only the workstation IP would help.

(11 Sep '12, 06:34) Derli Marcochi
Replies hidden

Well, feel free to edit and re-tag your question accordingly - from my own experience as customer, these topics surely get noticed by Product Management:)

(11 Sep '12, 06:48) Volker Barth

Thanks @Volker.

Could anyone from Sybase's Product Management review this enhancement request and indicate if this can somehow be implemented in a future release of SA 12?

(12 Sep '12, 13:29) Derli Marcochi
2

This enhancement request has been submitted to SQL Anywhere Product Management for consideration via support case# 11756493.

(03 Oct '12, 14:05) Ben Magazino

I started researching a workaround for this issue. I tried Volker’s suggestion and checked for the PROPERTY('IsNetworkServer') in the custom_prevalidation step of the maintenance plan. That didn’t help either because the email notification is always sent.

Is there any workaround to have a conditional email notification as part of a maintenance plan?

(12 Oct '12, 13:03) Derli Marcochi
Replies hidden

The maintenance wizard does not currently allow for custom actions based on SQL conditions.

If you attempted to add the above property check as part of the wizard code, the wizard explicitly says for this step:

Include Custom SQL

You can optionally chose to run a group of SQL statements at the start and/or end of the maintenance plan. These statements run regardless of whether the database is validated or backed up.

So this area does not allow you to customize the SQL logic for sending e-mail.


However, the maintenance plan wizard is really just a fancy Sybase Central way of executing a "CREATE EVENT" statement as Volker mentioned, with pre-populated stored procedure code.

If you are having your users create this event through the Sybase Central maintenance plan wizard, you can go back and edit the actual EVENT code underneath the "Events" tab - that's where you need to add in Volker's suggestion.

Is there any workaround to have a conditional email notification as part of a maintenance plan?

The e-mail section in the wizard has these options:

E-mail the report: (*) Always ( ) Only when the plan succeeds ( ) Only when the plan fails

(12 Oct '12, 13:54) Jeff Albion

I am not sure if we could ask customers to edit the event script after the fact as the maintenance plan is created via a script launched via InstallShield when our application is installed.

The reason we have asked for a review of the current options in the e-mail section in the wizard is that it would certainly allow this setting to be defined in the GUI by the users.

Something similar to:

E-mail the report: (*) Only from a network server

(12 Oct '12, 17:27) Derli Marcochi

I won't comment on whether such a GUI enhancement is useful or not - simply as I currently won't need is....


However, given the fact that (as Jeff has clearly stated) the maintenance plan facility is just a method to create according events in the database, I guess you might also think over

  • add a facility to automatically adapt the event code of the user-created plans - if you "invite" them to create such plans, you might also be able to identify the according events in the database and have some code to alter them as desired -
  • or replace the GUI facility with a selfmade way to create according events - if you study the automatically generated events, I would think it's not too difficult to build something comparable and add your particular requirements.

Just my 2 cents.

(15 Oct '12, 04:45) Volker Barth

@volker, I already tested with this as well and the issue with changing the event itself is that the changes done in the event itself are overwritten when a change is made in the maintenance plan using GUI (say time change, email address, etc.)

(15 Oct '12, 10:51) Derli Marcochi
1

OK, that would favour alternative no. 2 (replacing the GUI facility altogether)...

(15 Oct '12, 11:24) Volker Barth
More comments hidden
showing 5 of 11 show all flat view

It would seem to make sense to offer parameterizable conditional execution and someone may mine that thought out of this thread ... but it would be better to post it as a specific suggestion in an area set up for this.

Options include: = = = = = = = = 1 - A new posting with a title reflecting a possible enhancement suggestion 2 - Posting an Enhancement Request support case through Case-Express {http://case-express.sybase.com/cx/welcome.do see link under "Bugs/Enhancements" } 3 - or open up a dialog on the 'futures' newsgroup which was originally set up for that purpose.
{ newsgroup: sybase.public.sqlanywhere.product_futures_discussion http://www.sybase.com/detail?id=203857 }

permanent link

answered 27 Sep '12, 18:04

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×107
×72
×5

question asked: 10 Sep '12, 17:12

question was seen: 1,387 times

last updated: 15 Oct '12, 11:24