How many records are there?

On the surface, it may seem like an easy question. Data Owners typically think this has an easy answer.

If their data followed a very simplistic model, then it would have an easy answer. Just “select count(*) from table;” and report the value.

Unfortunately for Data Custodians, the data is often organized in relational data structures. In my case, we almost always buy a product and are not provided or not allowed to see schema documentation. So, this question requires making judgment calls.

  1. What kinds of data do I have? Hopefully, the tables have decent names that are meaningful. Learn how to use the product and the basic concepts of what things are will come with it. Assuming the tables are meaningful, then a review of the table names will suggest where the likely important data is stored.
  2. Research elsewhere if necessary. People blog about what they know. They ask questions in forums.
  3. Experiment. Change the value in the application and look to see whether it shows in the table.
  4. Count. Get the number of records for the relevant tables.

 

URI in a Database

This vendor’s application has been a headache for me for over a year now. We are getting close to upgrading, but there are some issues. One is why data in the new version is missing. I finally got the vendor to give me a query in the new version which was enough for me to figure out how to find the same data in the old version.

16686579989_02eba2c292
Shrug by Tom Hilton

Everything I check between the two databases is consistently the same across the systems. So, why are the user interfaces different? See image right.

I have done a lot of querying now trying to gain some insight into the black box since the front line support workers are not getting me helpful information out of their “additional resources.” I’ve asked to talk to a DBA, but they do not respond to that part.

Something hurts my brain is that every table’s primary key is name “uri.” Yes, everything is a Uniform Resource Indicator. (If everything is an uri, then effectively nothing is an uri.) But then when TableA needs to reference something from TableB, then it actually has a column that describes what it is. Except in this one case I am struggling to understand where the recordUri does not match the record table’s uri column.

So what does it match? See image.

An annotated schema would help out so much right now.

My guess is a table moved and broke a customization.

I suspect the choice of URI as an acronym is because of the HTTP protocol. For a web site address, one has the protocol in http:// and the full qualified domain name like ezrasf.com and everything starting with the next slash after is the URI. And that is what set me off to write this post.

SQL Server Alias Naming Breaks Convert Function

The vendor installs the application which includes creating the database and schema (SQL Server 2008 R2). The schema defined a certain column as varchar(128) that the application code expects to return an integer. If one of the entries for a client is a non-integer value, then HTTP 500 Internal Server Error failures prevent users from being able to login to the web site. The fix is relatively easy: update the problem value to an integer that is not already in use. This SQL returns “9999” even though the highest value is “12467” because the rules for sorting varchar are different than the int data type.

SELECT TOP 1 IdNum FROM table1 ORDER BY IdNum desc

No biggie, just convert it to an int.

SELECT TOP 1 CONVERT(int, IdNum) as IdNum FROM table1 ORDER BY IdNum desc

This works fine. The strangeness comes into play when the aliasing “as IdNum” uses anything other than the actual name of the column. I originally tried to rename it to compare to the first query so in my output I could compare both. If the alias is different from the column name, then the CONVERT gives in the varchar sorting result not the int as specified. These all fail.

SELECT TOP 1 CONVERT(int, IdNum) as IdNumConverted FROM table1 ORDER BY IdNum desc

SELECT TOP 1 CONVERT(int, IdNum) “IdNum2” FROM table1 ORDER BY IdNum desc

The CAST function behaves the same way. Something about the alias name appears able to break these conversions.

Removing “TOP 1” does not fix the behavior.

Oracle 11g

The highest revision of Oracle database supported by Blackboard for CE/Vista is 10g. Wondering if other Bb clients have noticed Oracle 10g leaves Premier Support on July 31, 2010. The first year of Extended SUpport fees are waived, so July 31, 2011 we’ll have to start paying extra money to Oracle unless Blackboard starts supporting 11g. (I’m guessing Blackboard isn’t going to discount what we have to pay to Oracle from the licensing costs. )

Maybe if enough clients complain, then Blackboard will start moving in the direction of supporting 11g? I’ve heard rumors of people already running 11g.

We Need a 4th Vista DBA / Technical Support

Work for OIIT!

Become our 4th DBA / technical support person for our team.

  • Located in Athens, GA (college town, UGA football)
  • $, benefits, generous leave, rare snow
  • we love open source
PDF of GeorgiaVIEW DBA position

Check out the PDF (right) for more information.

Sorry for the convoluted route to the application…

  • Click this link to go to our HR site.
  • Click the “View Job Postings / Apply for Job” link.
  • Check the “Information Instructional Tech” box.
  • Enter “learning” for the keyword and click search.
  • Systems Support Specialist 3” is our DBA position. We also have a Business Systems Analyst position for a less technical position.

We’d love to have you.

Finding Sessions

Clusters can making finding where a user was working a clusterf***. Users end up on a node, but they don’t know which node. Heck, we are ahead of the curve to get user name, date, and time. Usually checking all the nodes in the past few days can net you the sessions. Capturing the session ids in the web server logs usually leads to finding an error in the webct logs. Though not always. Digging through the web server logs to find where the user was doing something similar to the appropriate activity consumes days.

Blackboard Vista captures node information for where the action took place. Reports against the tracking data provide more concise, more easily understood, and more quickly compiled. They are fantastic for getting a clear understanding of what steps a user took.

Web server logs contain every hit which includes every page view (well, almost, the gap is another post). Tracking data represents at best 25% of the page views. This problem is perhaps the only reason I favor logs over tracking data. More cryptic data usually means a slower resolution time not faster.

Another issue with tracking is the scope. When profiling student behavior, it is great. The problem is only okay data can be located for instructors while designers and administrators are almost totally under the radar. With the new outer join, what we can get for these oblivious roles has been greatly expanded.

Certainly, I try not to rely too much on a single source of data. Even I sometimes forget to do so.

User Interface v. SQL Reports and Tracking

Blackboard Vista tracks student activity. This tracking data is viewed as a critical feature of Vista. Our instructors depended on the information until we revoked their ability to run reports themselves due to performance issues. Campus administrators can still generate reports (though some still fail). We doubt the solution to this is Blackboard improving the queries to create the reports. We favor deleting tracking data (data preserved outside of Vista) to resolve the performance issues.

We developed SQL reports to look at the tracking data where the user in question was not a student. Yes, the data is limited, but in determining when and where a user was active, can help determine where to look in logs. When we hit the performance issues we started using these reports where the user interface reports failed to generate.

My understanding was the user interface and SQL reports on tracking were the same. Both looked at the same data. The user interface reports were just sexier wrapped in HTML and using icons. I compared a user interface report to a SQL report. Just prior to doing this, I was thinking, WebCT was stupid for not tracking when students look at the list of assessments. Turns out “Assessment list viewed” was tracked in the user interface all along but was missing in our sqlplus queries. WTF?

The data has to be there. The problem has to be our approach in sqlplus is inadvertently excluding the information from the reports. Because these reports must be accurate, I’ll crack this nut… Or become nuts myself.

CRACKED THE NUT: So, part of the data WebCT collected was the name of pages. There is a page name table which was inner joined to the user action table. So pages without a name were not reported. George suggested an outer join. I placed it on the page name table which now lets us see the formerly missing tracked actions. For the specific case where I found this, I now get all the missing actions.

Considering a Blackboard (it’s their problem now) feature request to ensure every page in the application has a title. I consider it developer laziness (someone else said worthlessness) that some pages might not have something so core and simple.

ANOTHER TRICK: Oracle’s NVL function displays a piece of text instead of a null value. Awesome for the above.

Fun With Oracle Environment Variables

This is more or less for the next time I lose all my brain cells from not working over the weekend….

Critical Oracle variables:

  1. ORACLE_HOME
  2. ORACLE_SID

Pretty much any script that deals with Oracle needs the value of these.

Cron doesn’t have these variables, so use export VARIABLE to provide them to the shell. Also, “export VARIABLE=value” doesn’t seem to work. So use “VARIABLE=value; export VARIABLE”.

Oh, the joys of shell scripting.
🙂

Overheard In The Office

DBA: Some of you use stage in all lower case, some in all upper case, and some in mixed case. You have to all use one naming convention. The way you have written this, its not going to work.
Programmer: That is why I am here. I need you to fix their stuff so it will.
DBA: Do you know what a “Catch-22” is?
Programmer: No, but I have a feeling you are going to tell me.
DBA: Its when you do it one way which causes this to break. When you do it this other way it causes this other thing to break.
Programmer: Right, I just want to do it my way and it to work.
DBA: By breaking everyone else’s stuff?