I have a question about the error log generated during a data warehousing procedure.

Out of over 700,000 records, I've had 7 records through errors. Each session generates 7 data sets. 6 of the records have thrown errors on the same data set, but I get the error "Error committing dataset1 for . Some of the data may have been warehoused."

I have visually inspected the records and cannot find anything that jumps out to be in error.

The other record just says "Error processing ."

Is there any way to see expanded error messages, or to have the stored procedure echo back to me the SQL statement it is trying to generate?

BTW, going into SQL 6.5.

TIA,

Matt Sorrell

[email protected]

Hi Matt,

Well, you hit the nail on the head. When warehousing works, it works great, but when you have an error, the information is hard to find.

Right now, the best way to track down the problem is to modify your WP to get the error information from the DS/XO API. For example, using DSMethod(DS_GETERROR$, … ) (I think). You can also step through it in the debugger if necessary.

If you know the offending key, you can re-run just it by doing the following:

1) Set the selection to custom and give it the name "RERUN"

2) Modify the WP on select keys to check for "RERUN" and if so just call MAKE.LIST() with the key(s) you want to run

If you need more info, let me know and I can post some code.

Cameron Purdy

Revelation Software

Cameron,

If you could post some code, I would greatly appreciate it. I do the majority of my coding in AREV 3.12, and haven't had much of a chance to learn OI yet.

The warehousing procedure was simple enough I picked up on it pretty quick.

Unfortunately, I don't have the time to really get into the guts of OI.

TIA,

Matt Sorrell

[email protected]

Matt,

Here goes:

1) In System Editor, open procedure named "WP_" where is the name of the warehouse process you made.

2) Notice the various parts of the WP. Each of these is necessary in order for the manager to do what you tell it to do. For example, if you tell it to clear the tables by dropping and creating them and then warehouse everything, it will call the WP with the following instructions:

DropTables

CreateTables

ProcessInit "FULL"

ProcessRecord

ProcessRecord

ProcessRecord

…

ProcessRecord

ProcessRecord

ProcessTerm

In other words, it tells the WP to do the drop and create, then gets a list of items to warehouse (in the init), processes each one (in the process record portion) and then cleans up (in the term).

3) Let's say you want to process one records 1176 and 1177. Well, you just have to change the init section. Find the comment that looks like:

* the SQLExp_Select function selects the LH records to warehouse

… and change that chunk of code to do your own custom selection:

<code>
if Info _eqc "MYSTUFF" then
 * build a cursor with just the keys 1176 and 1177
 * (we could do a getlist or whatever ... any way to set up the cursor!)
  Make.List(0, 1176: @fm: 1177, "", "")
end else
 * the SQLExp_Select function selects the LH records to warehouse;
 * by encapsulating this functionality in a separate procedure,
 * addition warehouse types can be added without requiring changes
 * to individual Warehouse Procedures
  clearselect 0
  Ret=SQLExp_Select(Table, Info)
end

</code>

4) Handling errors is a bit more complex. Start by determining how you want to catch them. Here's three ways:

- sometimes you just want to know whether or not something went wrong … but you don't need any more details than that; in this case, just call Utility("BEEP") from the code and it will beep if that code gets executed

- sometimes you don't want to stop the running program but you want to know what is going on, for example, what parameters were passed; the best way is to use the System Monitor to display the information using the Send_Info(text) function; before you do this, open System Monitor and run the following command line in System Monitor to prevent the normal massive flow of text that it displays:

run SetDebugger "SPY", 0

- in dev mode, I prefer to just drop to the debugger to analyze the data being warehoused; I often do a Send_Info followed by a DEBUG statement (so I can just hit "run" to keep going and see the info later):

Send_Info(text)

debug

5) All the potential errors that you can catch in the WP are in the DS API, so you get the error info from DSMethod; for example, in the ProcessRecord section, change:

<code>
if Ret else return

</code>

to:

<code>
if Ret else
 * change  to whatever DS is being processed
  DSMethod(Handle, DS_GETERROR$, "", "", "", "", Text)
  Send_Info("WP Error:  ": Text)
  debug
end

</code>

That should get you started. Best of luck,

Cameron Purdy

Revelation Software

Matt,

I forgot to mention: To get your custom selection to be called, go into the schedule dialog in the warehouse manager (just double click on a WP in the list before pressing Start) and choose Record Selection, Custom, and type in the name (like "MYSTUFF") that will get passed to the init portion of your WP.

Cameron Purdy

Revelation Software

Cameron,

Thank you very much for your assistance. As soon as my server is back up, I will try and implement your suggestions.

Matt

View this thread on the forum...