Using the SQL-NS Debugging Facilities


This section describes the tools and techniques you can use to debug the rules in your SQL-NS applications. After reading the material in the previous section, you should have a good understanding of the environment in which the rules run. The information in this section explains how to mimic that runtime environment so that you can execute rules manually to debug them.

Overview of Debugging Tools and Techniques

To debug rules in SQL-NS applications, the following approach is generally used:

1.

Disable the notification generator so that it does not try to execute rules as you are debugging them.

2.

Submit a batch of events to the SQL-NS application.

3.

Manually set the quantum clock to a time period that encompasses the arrival time of the event batch. SQL-NS provides stored procedures for setting the quantum clock.

4.

Use a SQL-NS stored procedure to create a new debugging quantum and set up its runtime environment, including materializing the views of the event and subscription data.

5.

Examine the event and subscription data in the views to see the exact input the rules will run against. At this point, you may run some queries that implement part of the matching criteria in your rules to test their operation. For example, if you're trying to debug a match rule that does not produce the right notifications, you can run a series of queries that implement parts of the FROM and WHERE clauses of the rule's SQL statements to determine which parts are responsible for the unexpected results.

6.

Use a SQL-NS stored procedure to manually execute the actual rules one by one. After each rule executes, you can look at the results it produced, as well as the general state of the application.

7.

If you find a problem with a rule, you can fix it in the ADF and then update the instance to apply the changes to the database. You can then either run the rule manually again to verify that it works correctly, or you can start the instance normally and allow the rule to be executed by the generator.

The key to this debugging approach is step 3, in which you set the quantum clock to a specific time. SQL-NS provides two stored procedures for setting the quantum clock: NSSetQuantumClock and NSSetQuantumClockDate. The first one sets the quantum clock back to a previously processed quantum. The second stored procedure sets the quantum clock to a specific quantum start time, which may or may not have been the start time of any previously executed quantum. Whichever stored procedure you use, a new quantum is created for debugging purposes. If you use NSSetQuantumClock, the new quantum will have the same start and end time (according to the quantum clock) of a previously executed quantum. If you use NSSetQuantumClockDate, the new quantum will have the start time you specify and an end time of one quantum interval later.

Note

All the SQL-NS debugging stored procedures explicitly check that the generator is disabled before performing any debugging operations. If the generator is not disabled, these stored procedures return an error.


To set up the runtime environment for executing rules after setting the quantum clock, SQL-NS provides a stored procedure called NSPrepareRuleFiring. This populates the event and subscription views for the debugging quantum and sets up any temporary tables and states used internally when executing rules. After calling NSPrepareRuleFiring, you can examine the views and other application state manually.

When you are ready to execute rules, you can invoke NSExecuteRuleFiring. This invokes the first rule that needs to be executed. After running the rule, you can check its results and the general state of the system. Calling NSExecuteRuleFiring again executes the next rule in the application. You can continue calling NSExecuteRuleFiring until all the rules have been executed.

In addition to the stored procedures for running rules manually, SQL-NS also provides a set of stored procedures you can use to debug problems with schedules in scheduled subscriptions. Subscription schedules can sometimes be confusing because you specify a start time and a recurrence pattern when you create the subscription. With only this information, it can be difficult to deduce which subscriptions will be evaluated at a particular time of day. SQL-NS provides two stored procedures, NSScheduledSubscriptionDetails and NSScheduledSubscriptionList, that use the generator's schedule-scanning algorithm to evaluate subscription schedules and return the list of subscriptions due to be evaluated during a given time period. If you're not seeing the expected results from your scheduled rules, you can use these stored procedures to determine which scheduled subscriptions should have been evaluated.

In the following sections, we use the techniques and stored procedures described here to debug the music store application. Although there shouldn't be any problems with the rules in the application at this point, walking through these steps will show you how to go about examining and executing rules manually. You can apply these techniques in your own applications if you need to debug your rules.

Preparing to Debug: Disabling the Generator and Submitting Events

Before we begin debugging, make sure that you have the music store instance created and running on your system. Unless you explicitly cleaned it up, it should still be there from previous chapters' work. If you did clean up the instance, make sure that you re-create it according to the instructions in Chapter 10, "Delivery Protocols," (see the "Re-creating the SQL-NS Instance" section, p. 362). After re-creating the instance, you must add subscribers and subscriptions againyou'll find instructions for doing this in the "Adding Subscriber Devices for New Delivery Channels" section (p. 365) in Chapter 10.

Before using any of the stored procedures to debug the rules in an application, you must first disable the generator to prevent it from trying to process quantums in the normal way while you are doing manual rule execution. To do this from the command line, you invoke nscontrol disable, passing the -generator argument to indicate that you are disabling just the generator component, not the whole instance. The batch file, disable_generator.cmd, in the C:\SQL-NS\Samples\MusicStore\Scripts directory invokes nscontrol disable this way. Note that we cannot use the disable.cmd script we've been using in previous chapters because that script disables the whole instance, rendering all components inoperable. We still need to submit events during our debugging, which isn't possible if the whole instance is disabled.

Take a look inside disable_generator.cmd. The nscontrol disable command is issued as follows:

 nscontrol disable -n %INSTANCE_NAME% -generator %SQL_USER_INFO% 


As before, the -n argument specifies the instance name, and the %SQL_USER_INFO% environment variable provides a SQL username and password if you're using SQL Server Authentication. The -generator argument specifies that only the generator should be disabled. As written, the command disables the generator for all applications in the instance (we have just one). To disable the generator for just a single application, in the case that your instance has more than one application, you can use the -application argument (in addition to the -generator argument) to specify an application name.

Note

For more information on disabling individual components, including techniques for doing these operations from Management Studio, see the "Enabling and Disabling Components" section (p. 492) in Chapter 14.


Run disable_generator.cmd from the C:\SQL-NS\Samples\MusicStore\Scripts directory, in a Notification Services Command Prompt, to disable the generator for the music store instance. You should see output indicating that the generator component is either in the Disabled or Disable Pending state.

Now we need to submit a batch of events that we can use to test the rules manually. Start the AddSongs program that we created in Chapter 8, "Event Providers," and log in to your SQL Server when the connection dialog box appears. If you need to, refer to the section "The AddSongs Program" (p. 243) in Chapter 8 for instructions on how to build and run the AddSongs program. Because we want AddSongs to act as an event provider, you should check the Submit Events for Songs Added box whenever you use AddSongs in this chapter.

Type the following song data into the AddSongs user interface (again, it's essential that you specify the artist name correctly; otherwise, the events won't match the subscriptions):

Album Title: The Girl in the Other Room
Artist Name: Diana Krall
Genre: Jazz
Song 1: Almost Blue
Song 2: Temptation

Make sure that the Submit Events for Songs Added box is checked; then click the Add to Database button. Verify that the events were submitted correctly using the following instructions:

1.

Open Management Studio and log in to your SQL Server.

2.

Open a new query window and issue the following query:

 USE MusicStore GO SELECT * FROM [SongAlerts].[NSSongAddedEventBatches] 


The resultset shows a row for each submitted event batch. The last row should be the event batch just submitted. You can verify this by checking the StartCollectionTime and EndCollectionTime values (note that these are expressed in UTC). You can also verify the event data by looking at the rows in the events table that have the event batch ID of the last event batch row.

Note the event batch ID of the event batch you just submitted, as well as the EndCollectionTime value. You'll need these values when setting the quantum clock in the next section.

Setting the Quantum Clock

To run rules against the event batch we just submitted, we need to set the quantum clock to a time period that encompasses its EndCollectionTime. The SQL code shown in Listing 11.2 accomplishes this.

Listing 11.2. Setting the Quantum Clock

 USE MusicStore GO DECLARE @EndCollectionTime DATETIME DECLARE @QuantumStartTIme DATETIME --Obtain the EndCollectionTime of the event batch SELECT @EndCollectionTime = EndCollectionTime FROM [SongAlerts].[NSSongAddedEventBatches] WHERE EventBatchId = /*... fill in your event batch ID here ...*/ --Calculate a quantum start time of 5 seconds before the --EndCollectionTime of the event batch. SELECT @QuantumStartTime = DATEADD(second, -5, @EndCollectionTime) --Set the quantum clock EXEC [SongAlerts].[NSSetQuantumClockDate] @QuantumStartTime 

The code in Listing 11.2 declares two local variables: one to hold the end collection time of the event batch, the other to hold the start time of the debugging quantum that will be created. After declaring the variables, the code obtains the EndCollectionTime value of the event batch by selecting from the event batches table. Pay particular attention to the WHERE clause in this query (shown in boldface font in Listing 11.2). This is where the event batch ID is specified.

You will find this code in the file C:\SQL-NS\Chapters\11\Scripts\SetQuantumClock.sql. Open this file in Management Studio and edit the WHERE clause of the query to specify the ID of the event batch you just submitted. For example, if your event batch ID was 7, the WHERE clause would read:

 WHERE EventBatchId = 7 


The next line calculates a start time for the debugging quantum by subtracting 5 seconds from the EndCollectionTime of the event batch. Because the music store application's quantum duration is 15 seconds, this ensures that the quantum will include the event batch. Technically, you can set the quantum start time to be the exact EndCollectionTime because the generator includes event batches that arrived exactly on a quantum start boundary, but adding a little buffer certainly doesn't hurt. The last line of Listing 11.2 invokes the NSSetQuantumClockDate stored procedure to set the quantum clock to the calculated start time.

Run the code in Management Studio now. The commands should complete successfully. You won't see any result data, but the quantum clock will be set so that a new debugging quantum will be created when we invoke NSPrepareRuleFiring in the next section.

Note

Instead of setting the quantum clock to a specific date and time with NSSetQuantumClockDate, you can use the NSSetQuantumClock stored procedure to set the quantum clock back to a previously executed quantum. You can then rerun the rules that were run when that quantum was originally processed. When rerunning a quantum this way, the event and subscription views are populated with the same data they contained during the original processing of the quantum.

To use NSSetQuantumClock, you need to first obtain the quantum ID of the quantum you want to execute. You can get this by looking at the quantum records in the NSQuantum1 table in the application schema.

NSQuantum1 maintains a record for every quantum that the generator executes. The StartTime and EndTime columns in this table indicate the start and end times of the quantum, according to the quantum clock. The RtcStartTime and RtcEndTime columns indicate the real clock times at which the quantum processing started and ended. Note that all time values are expressed in UTC. Identify the quantum you want to execute by looking at the start and end times and then make a note of the QuantumId. Pass this QuantumId to NSSetQuantumClock.

Alternatively, to find the quantum ID of the quantum in which a particular event batch was previously processed, you can use the view NSEventBatchView also in the application schema. This view contains a row for every event batch submitted to the application. Columns in the view indicate the event batch ID, the event class name, the start and end collection times, and the quantum ID of the quantum in which the batch's events were processed. Event batches that have not yet been processed have NULL in the QuantumId column, but those that have been processed will show a valid quantum ID.


Preparing for Rule Firing

After the quantum clock has been set, you call the NSPrepareRuleFiring stored procedure to create a new quantum, set up the views and other generator state, and get ready to execute the first rule. NSPrepareRuleFiring is located in the application schema (like all the other debugging stored procedures) and does not take any parameters, so you can invoke it as follows:

 EXEC [SongAlerts].[NSPrepareRuleFiring] 


Run this command in a Management Studio window after setting the quantum clock (using the code shown in Listing 11.2). Two resultsets are returned. The first resultset describes the new quantum and the rule that is set up to run. In the case of the music store application, the first rule that runs after a batch of SongAdded events is submitted is the chronicle rule that updates the event history. You see the name of this rule in the RuleName column of the first resultset. The second resultset indicates the names of any chronicles that the rule might affect. In this case, the resultset shows the SongAddedChronicle. If the rule was not a chronicle rule, the second resultset would have been omitted.

After running NSPrepareRuleFiring, you can examine the event and subscription views. Run the following query to see the data in the events view:

 SELECT * FROM [SongAlerts].[SongAdded] 


The data returned contains the song IDs of the two songs just added to the music store (when you invoked the AddSongs program earlier). Notice that we're selecting directly from the SongAdded view that the rule references, not from the events table, NSSongAddedEvents. This would not have returned the correct data had we not set the quantum clock and set up the generator state with NSPrepareRuleFiring. As you can see, the events view contains only the event data against which the rules should run; the events table may well contain other event data from previously processed event batches, but that data is not made visible to the rules.

Having properly set up the quantum state, you can also see the subscriptions against which the rule will run by executing the following query against the subscription class view:

 SELECT * FROM [SongAlerts].[NewSongByArtist] 


Again, this query selects directly from the view that the rule references, not from the underlying table.

At this point, if you were having problems with your rule logic, you could run test queries against the views. Because the views are set up as they would be during normal rule execution, these test queries would operate against the same, real input data that the rules themselves use. Although you could execute the actual rules as specified in the ADF, you should not do this directly for any rule that inserts into a notification view. The triggers on notification views rely on additional state that must be set up before they are invoked.

To properly execute rules that insert into a notifications view, use the NSExecuteRuleFiring stored procedure, as described in the next section. (NSExecuteRuleFiring is also the recommended way to run rules that don't insert into a notification viewfor example, chronicle rules.) Alternatively, you can execute the queries that obtain the data the rules insert into the notification views, without actually performing the inserts. If a match rule uses an INSERT-SELECT statement (as the music store application's match rules do), this is as simple as removing the INSERT line and running the SELECT statement on its own. Doing this gives you an opportunity to see the data the rule would normally insert into the notification view.

Executing Rule Firings

The NSExecuteRuleFiring stored procedure is used to execute the next rule in the quantum that is ready to run. Immediately after running NSPrepareRuleFiring as instructed in the previous section, the first rule to run will be the chronicle update rule. NSExecuteRuleFiring does not take any arguments, so you can invoke it as follows:

 EXEC [SongAlerts].[NSExecuteRuleFiring] 


NSExecuteRuleFiring returns up to three resultsets. The first resultset describes the rule that ran. The second lists any chronicles that might have been affected. In the case of rules that do not affect chronicles, this resultset is omitted. The third resultset describes any notification batches generated as a result of executing the rule. For the first chronicle rule, no notification batches are generated.

Note

NSExecuteRuleFirings isn't always capable of reliably determining whether a rule affected any chronicles. Sometimes you will see a chronicle table name listed in the second resultset, even if that chronicle was not affected.


After executing one rule, you can invoke NSExecuteRuleFiring again to execute the next rule in the quantum. In the case of our music store application, this will be the eventtriggered match rule for the NewSongByArtist subscriptions. This rule will generate notifications (because the events match some of the subscriptions), so when you invoke NSExecuteRuleFiring again, the third resultset will show a row describing a new batch of notifications.

Between each rule firing, you can examine the state of the system. This allows you to see the effects of each individual rule and isolate any problems that might exist. You can continue running NSExecuteRuleFiring repeatedly until there are no more rules to fire for the quantum. When this state is reached, the first resultset returned from NSExecuteRuleFiring indicates a NULL rule name.

Using the Scheduled Subscription Debugging Stored Procedures

The NSScheduledSubscriptionList and NSScheduledSubscriptionDetails stored procedures allow you to obtain information about the scheduled subscriptions that will be evaluated during a particular time period. Both stored procedures are created by the SQL-NS compiler in the application schema. NSScheduledSubscriptionDetails returns the subscription data for all subscriptions in a subscription class that are scheduled to fire between a given start and end time. NSScheduledSubscriptionList returns a list of all the scheduled subscriptions for a particular subscriber, across all subscription classes, that are scheduled to fire during a given time period.

Adding Scheduled Subscriptions

To see the scheduled subscription debugging stored procedures in use, we need to add some scheduled subscriptions to the application. To do this, we'll use the same SubscriptionLoader tool we used to test scheduled subscriptions in Chapter 6. Use the following instructions to add scheduled subscriptions with SubscriptionLoader:

1.

Invoke SubscriptionLoader.exe from the C:\SQL-NS\Tools\bin directory on your development machine.

2.

In the Connect to SQL Server dialog box, enter the name of your SQL Server and select an authentication mode. If you select SQL Server Authentication, enter the login name and password of the development account you created in Chapter 2, "Getting Set Up." Click OK to connect.

3.

In the main SubscriptionLoader dialog box, select the MusicStore instance, the SongAlerts application, and the NewSongByGenre subscription class (be careful not to select the event-triggered subscription class, NewSongByArtist, which is also offered as a choice).

4.

In the next section of the SubscriptionLoader dialog box, the Subscriber ID dropdown list shows the three subscribers we added to the music store instance. Select any of these subscribers.

5.

Enter values for the subscription fields in the data grid. For the GenreName field, enter Jazz, Rock, or Hip Hop. For the DeviceName field, enter WorkEmail and for the Locale field, enter en-US.

6.

Specify a subscription schedule in the bottom section of the SubscriptionLoader dialog box. In the Schedule Start Time field, select a start time 2 or 3 minutes into the future. Select your local time zone in the Timezone field (it should be preset to your local time zone, but you can change it as necessary). Note that the initial value shown in the start time field is the time at which the program was startedthis value is not updated, so you may have to add several minutes to it if you've had the program open for a while.

7.

Click the Add Subscription button. You should see a confirmation dialog box telling you that the subscription was successfully added.

8.

Repeat steps 4 through 7 to add subscriptions for several subscribers. Use a variety of GenreName values, but always enter WorkEmail and en-US for the DeviceName and Locale fields, respectively.

Using NSScheduledSubscriptionDetails

Listing 11.3 shows the SQL code that invokes NSScheduledSubscriptionDetails to obtain information about the NewSongByGenre scheduled subscriptions due to fire in a 10-minute period around the current time.

Listing 11.3. Use of the NSScheduledSubscriptionDetails Stored Procedure

 USE MusicStore GO DECLARE @StartTime DATETIME DECLARE @EndTime   DATETIME SELECT @StartTime = DATEADD(minute, -5, GETUTCDATE()) SELECT @EndTime = DATEADD(minute, 5, GETUTCDATE()) EXEC [SongAlerts].[NSScheduledSubscriptionDetails] N'NewSongByGenre', @StartTime, @EndTime 

The code declares local variables to hold the start and end times of a period that extends from 5 minutes before the current time to 5 minutes after the current time. It then calls NSScheduledSubscriptionDetails, passing the name of the NewSongByGenre subscription class and the start and end times. Note that all times are expressed in UTC.

The code in Listing 11.3 is in the file C:\SQL-NS\Chapters\11\Scripts\ScheduledSubscriptionDetails.sql. Load this file into Management Studio and run it. If you run it within 5 minutes of the start times of the scheduled subscriptions you added, the resultset returned will contain the scheduled subscription data. If the schedule times for the subscriptions you entered fall outside the 10-minute window specified in the script, add a new set of scheduled subscriptions with start times closer to the current time. You can then run this code again to see the subscription data.

Using NSScheduledSubscriptionList

Listing 11.4 shows the NSScheduledSubscriptionList stored procedure in use.

Listing 11.4. Use of the NSScheduledSubscriptionList Stored Procedure

 USE MusicStore GO DECLARE @StartTime DATETIME DECLARE @EndTime   DATETIME SELECT  @StartTime = DATEADD(minute, -5, GETUTCDATE()) SELECT  @EndTime = DATEADD(minute, 5, GETUTCDATE()) EXEC [SongAlerts].[NSScheduledSubscriptionList] N'Emily', @StartTime, @EndTime 

Again, the code calculates a start and end time by subtracting and adding 5 minutes to the current time. It then calls NSScheduledSubscriptionList, passing a single subscriber ID and the start and end times. This returns all the scheduled subscriptions associated with that subscriber, from all subscription classes, scheduled to fire during the given time period.

You'll find the code in Listing 11.4 in the file C:\SQL-NS\Chapters\11\Scripts\ScheduledSubscriptionList.sql. Open this in Management Studio. Notice that the script passes the value "Emily" for the subscriber ID argument to NSScheduledSubscriptionList. If "Emily" was not one of the subscribers for whom you added scheduled subscriptions, you'll need to edit the script. Replace "Emily" with one of the subscriber IDs you selected in step 4 of the instructions in the "Adding Scheduled Subscriptions" section (p. 411).

Run the script within 5 minutes of the start times of the scheduled subscriptions you entered. In the resultset, you should see the data for the subscriptions associated with the given subscriber ID.

Reenabling the Generator

As explained earlier, the debugging stored procedures described in this chapter require the generator to be disabled before they can be used. The section "Preparing to Debug: Disabling the Generator and Submitting Events" (p. 405) described how to disable the generator using the nscontrol disable command with the -generator argument. While the generator is disabled, you can run the debugging stored procedures, but the application does not process new event batches on its own.

At the end of a debugging session, you may want to reenable the generator to let the application resume normal operation. To enable the generator, you can invoke the nscontrol enable command with the -generator argument. The -generator argument used with nscontrol enable specifies that the command should apply only to the generator component (just as it did when used in the nscontrol disable command earlier).

In the C:\SQL-NS\Samples\MusicStore\Scripts directory, you'll find the batch file, enable_generator.cmd, that invokes nscontrol enable with the -generator argument. Take a look at the contents of this file to see how nscontrol enable is called (notice that the command-line arguments are identical to those in the nscontrol disable call you saw earlier). Run enable_generator.cmd to reenable the generator component on your system. Alternatively, you can invoke the enable.cmd script to enable the whole instance.




Microsoft SQL Server 2005 Notification Services
Microsoft SQL Server 2005 Notification Services
ISBN: 0672327791
EAN: 2147483647
Year: 2006
Pages: 166
Authors: Shyam Pather

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net