Monday, January 22, 2007

Employers increase at-work monitoring

Bob Weinstein's "Tech Watch" (printed in Monday Washington Times "Recruitment Times") column today as "Big Brother Is Watching You."

In fact, when I worked as a site rep for Univac in 1972 at Public Service Gas and Electric in downtown Newark, NJ, I wrote a utility to monitor the system log files and report on each employee's use of every system facility. In olden days, employers actually worried about how many compiles it took a programmer to get a program working (when we had punch cards and one-day turnarounds -- although even then Univac had good terminals for programmers).

Of course, today the term refers to monitoring employees email and Internet access for inappropriate personal use. In the early 1980s, when I worked for Chilton in Dallas, TX, the company warned us early that it audited our accounts for personal use on the Ahmdahl mainframe (at the time, personal computers at home were just coming into wide use -- I bought a TRS-80 in 1981). Even school homework was forbidden. "I you use company resources for any personal purpose at all, you will be terminated," a memo warned.

As Weinstein writes today, the biggest security threat for companies could be employees' visiting dangerous sites and inadvertently loading new malware on their work computers. It gets more complicated when you take a laptop home, but people have been fired when workers' kids have used company laptops without the parents' knowledge. It's even more complicated when you use your own computer (more common as people work at home as contract customer service agents for companies like Alpine).

Some companies solve the problem by limiting the kinds of access possible. When I worked for a debt collector in 2003, we dialed in to a Unix or VAX application and gave an IP address, but the network had no access to the Internet. Pretty safe.

Of course, the modern problem, already written about in previous blog entries, is the reputation incurred by employees with their own blogs or social networking profiles -- and the possibilities that these could indavertently compromise an employer's confidential information.

All of this needs to be kept in perspective. And it is a lot.

Sunday, January 21, 2007

Review Topic: DB2 SQL Unions, Joins

A UNION is a function that combines the results of two or more SELECTS with all columns and all rows encountered. To prevent a misleading result, it is a good idea to use the AS clause. For example, a UNION of a SELECT from an inventory table and a back-order table could imply that parts are on hand when they are not, when the same column (quantity) appears in both tables, unless you rename one of these with an AS.

Many people code INNER JOINs with implicit syntax (coding the actual rule) but it is better to code with an explicit syntax (INNER JOIN ... ON). Professional coding standards regard INNER JOINS as easier to read. Qualified names can be used with joins.

OUTER JOINS may be full or left or right. A right join can be changed to a left by switching the statement order.

Outer joins are used when it is desirable to keep unmathched rows in a result. For example, a substitute teacher system might want to show inactive or expired subs (those who have accepted no assignments within the reporting period) for investigatory purposes. Call the Full Outer Join a "background investigation." The VALUE or COALESCE function can return a perdetermined string for a null value.

Picture: Railroad on Mississippi coast a few months after Katrina

Thursday, January 18, 2007

Rview Topic: DB2: Commits, Cursors and Rollbacks

Back in 1974 I had some practical experience with the concept of a Database rollback when working in St Paul MN for Univac on a Bell Labs benchmark with a Univac 1110, where the database was DMS-1100, comparable to IDMS in the IBM world. Whole run-units would roll back and get caught in a catch-22 deadly embrace, degrading performance and causing required norms for the benchmark to be missed for several weeks. Finally, an analyst noticed that in one of the transactions, the problem could be fixed by reversing the order of two statements.

Often, with a DB2 cursor, the best coding technique will be to use somewhat frequent COMMIT commands with the WITH HOLD option (on the DECLARE CURSOR), so that the cursor does not close and have to be re-opened and re-positioned. With the option coded, a FETCH command will retrieve the next row in sequence according to the primary key for the table.

If there are no COMMIT commands, no updates get written until the program ends normally. Performance can degrade and contention may result. DB2 will rollback the updates upon an abend, or when a ROLLBACK is coded.

Wednesday, January 17, 2007

Review topic: DB2, referential integrity

Referential integrity is an important concept in relational databases. It means that each row of a dependent table will have a foreign key with a value equal to the value of a primary key in the parent table. The names of the fields may and will generally be different. A foreign key comprises a column (or columns) in a dependent table that correspond to the primary key in the parent table. So even in a relational database, the concept of "hierarchy" can exist in a somewhat artificial manner.

With DB2 (2.1) the database will enforce referential integrity with the CREATE TABLE statement.

When you INSERT, every row in the parent table must have a unique primary key, and every row in any dependent table needs a foreign key corresponding to the primary key value. You can't change the primary key with an UPDATE if any foreign keys in dependent tables refer to it. The DELETE statement has CASCADE, RESTRICT, and SET TO NULL options to handle dependent rows in a manner decided by the applications designer.

The SQLCODE value for a referential integrity error is -500.

I once worked on the conversion of an IMS name-and-address database to DB2 (back in 1999). An independent consultant designed all of the tables, and referential integrity was one of his first concerns. The design took several months, which is typical in any major conversion.

(Picture, unrelated: Paw-Paw tunnel on C&O Canal, Maryland, on Potomac River)

Monday, January 01, 2007

Review Topic: SQL: Firehose cursors, and connection pooling

In working with Mirosoft SQL Server 2005 along with Visual Studio .NET 2005 and ADO.NET databases, a couple of concepts:

Firehose cursors: The SqlDataReader class fetches rows one at a time with a lock at on one row at a time. (A cursor means "current set of rows.")

uses internal SQL format instead of DataSet (to convert intenrally to XML )for internal representation of data. This saves time at the expense of flexibility.

The .NET framework uses connection pooling, managing already-open connections to the database in a pool, to save resources in constantly opening and closing connections.