The Best Thing I Never Learned

The best thing I never learned was that I couldn't do something. It just doesn't occur to me that I can’t do something, particularly if it involves a computer. I’ve embarked on a number of projects over the years convinced that I would get them done one way or another, and a few of them ended up being pretty challenging. Which were, of course, also the ones I learned the most from. I also really hate to lose. Fueled by fearlessness to begin and a stubbornness not to give up, I share three of my fondest memories.

Gothic was the name of a computer program that would read a single input card and print it out on a old-style mainframe impact printer, transforming each character into many rows of asterisks that formed a large, gothic-font representation of the character. Those printers used boxes of paper that were one long continuous page, with perforations that allowed different printouts to be separated, so the Gothic program could print large, long banners, perfect for a “Happy 30th Birthday” sign. Gothic was written in Assembler language for a DOS (IBM, not Microsoft, the PC hadn’t been invented yet) operating system, but Wright State University used MVT and Gothic needed to be modified to run. I really wanted Gothic available, so I decided to figure out how modify it. The only problem was I didn’t know assembler language and it was totally Greek to me. Little by little I learned that a BALR was a Branch-And-Link-Register and that mainframes has 16, 32-bit registers. An MVC was a Move-Character and an S was a Subtract. And those translated exactly to machine instructions and that meant most of the program would work on MVT without change. That was a relief. I also figured out that there were only three things that had to change, the beginning of the program where control was passed from the operating system to the program, the end where control was passed back, and several operating system-specific inclusions which I learned were called macros. All those changes totaled perhaps a dozen lines of code, but a wealth of knowledge to me. And a bit of pride each time I saw a Gothic banner taped to a wall.

COBOL, an acronym for COmmon Business-Oriented Language, was one of the required programming languages to learn way back then, and Wright State used an interpretive version from the University of Waterloo named WATBOL, which was more efficient for student use. My final program of the quarter kept giving me errors and as much as I looked, I couldn’t spot what was wrong. One trusty way to debug a program was to insert PRINT statements at key places to verify the program was moving through its logic as expected. After much trial and error, I found that if I included a single PRINT statement at a specific spot, the error went away, which pointed to an error in WATBOL itself, not in my code. That’s a pretty hard conclusion to sell to your instructor, so I offered another solution. I would do all the work necessary to convert my program to real COBOL and convert all the input and output files from WATBOL format to regular operating system format, not really knowing if that was possible. And I would do it all in the week break between quarters. He agreed to delay my final grade for that week and another intense learning experience began. I had to learn enough JCL (Job Control Language) to create files and execute the COBOL compiler, find some utilities to do the file conversions and make the minor code changes due to the differences between WATBOL and COBOL. I was helped by having all the other students on break and no contention for computer resources. By the end of the week I had everything converted and successfully ran my program, without the PRINT statement, and turned in my completed assignment.

Years later, somewhere in the mid-90’s, I was taking a graduate-level Operations Research class where we were learning optimization techniques like Linear and Integer Programming and I needed an idea for a final project. At the same time I was managing the Network Systems group at The Mead Corporation, and they were in the process of acquiring a distribution business to add to their Mead Merchants group. My group had to plan and execute a networking solution for 30 new locations located all over the United States at the lowest cost while delivering good performance. Networks back then consisted of multi-drop lines, where you started a circuit at your data center, added a drop in an office in one city, added another drop in a second city, etc. We engaged AT&T to design a network for the new locations based on three rules. To deliver good performance, the expected busy-hour line utilization for each line could not exceed 50 percent (based on sales volume and line usage from existing Merchant locations) and a maximum of four cities per line. For high availability at the large regional offices, they had to be connected to two different lines. AT&T was given these specifications to feed into their world-class INOS design tool. And it occurred to me that this might make a good final school project. Took me a week to figure out that I would need to design an Integer Program consisting of 30 rows, each one representing an office and a large number of columns, each one representing any valid line given the performance constraints. The regional offices would just look like two locations with one-half the expected load each. The total distance from the data center to all the locations on a potential line would be used as an estimation of its cost. Add a few other constraints and solve for the lowest cost. In my head, it was possible, but a problem vastly too large to commute by hand, as we learned in class. But Mead had MPSX, a mainframe-based program for solving Integer Programs like this, so I learned enough about its syntax before writing two PL/1 programs to generate the matrix, which turned out to have over 20,000 columns. That completed, I submitted the batch job to run overnight before leaving for home, not knowing how long it might run. To my shock, it was still running the next morning. All this work and my final project was in danger. I cancelled the job and began trying to figure out what had happened. I noticed that most of the job’s activity was constant reading and writing to MPSX’s matrix disk datasets, so I changed the JCL to keep those datasets in memory and again submitted them for overnight execution, which it did, barely. I was at the edge of how large a problem MPSX could handle. Over the following week several changes were made and a final solution was given to AT&T to price and compare to INOS’s solution. I won by $100 per month, a small but decisive victory, and earned an A+ from my instructor.

Now that’s what I call having fun.

The Pocket Directory Story

Back in the 1990’s I managed the Network Services team with responsibility for all voice and data communications. The team installed and upgraded phone systems, ran coax cables, installed hundreds of LAN switches from Austria to Japan. It was an exciting time as this thing called “The Internet” started to gain some traction and mobile phones started fitting in the palm of your hand. Then one day, along with all that, the CIO asked (OK, told) me that I now had responsibility for the fixing and reprinting, asap, the small, pocket-sized, Corporate phone directory which had been totally and embarrassingly messed up. That part was easy, but now that I had this in my control, and I really hated this little thing, I had my team carve out some time to make it better … way better. We discussed its shortcomings (I’m being polite), came up with ideas and asked other local companies how their’s looked. The following documents the highlights and shows what refusing to accept status quo and embracing change can fix, even with something as pedestrian as a phone directory.

1. We changed from glossy to uncoated paper

One constant in life is change and that certainly is true for people and telephone numbers over the yearly life of this directory, so writing updates is a common practice. But glossy paper is hard to write on and easily smears. Sure, it looks all pretty and shiny, but this isn’t an advertising brochure. Switching to uncoated paper solved this, and was less expensive.

2. We changed from a glued back to a spiral binder

What does one do with a telephone directory? Not a trick question, you look up phone numbers. Why do you look up phone number? To call someone. Can you remember a 10-digit phone number? You are in the minority if you can. I’m with the majority who can’t. The old directory had a glued back to it, so you had to hold it in one hand while holding the phone another one hand and dialing with your third hand. Oops, ran out of hands. So you held the phone under you chin and dialed. That was comfortable (not). The other option was to break the glue so the directory would lay flat. That led to pages falling out. Switching to a spiral binder solved all that nonsense.

3. We changed from portrait to landscape

The old portrait layout resulted in multiple lines per person and generally a messy looking layout. Switching to landscape permitted a person’s name, title, work and home telephone numbers to fit in a single line, and we switched to a consistent 10-digit phone number format. Improved the readability at least ten fold. We also made it a little larger, but still kept it under the size of a standard dress shirt pocket.

4. The entire network team did a QA check

Unlike many IT departments, the network team knows people and had visited about everywhere in the company. The biggest source of recurring errors in the content of the the directory was the process used to collect the data. The old process was basically just send us your department’s updates and we’ll print another book. No updates, we’ll print last year’s. Hence any errors in content was someone else’s fault. I just wanted to make the best directory possible, not cover my backside when mistakes were made. So after the first draft of the new directory was ready we sat down over pizza and soda’s to look for mistakes, over the objections that we would be wasting our time. Page one, the Board of Directors. A must-get-it-right page in any large corporation. Yep, first error found. “But they said it was OK!”. It wasn’t a blame game any more. My favorite was when we got to the Hawai’i office and Charlie jumped up exclaiming “That’s not the right main office number, I just know it!”, ran to phone dialed it with his expected result, then dialed what he knew was right, had a brief conversation with the Hawai’ian receptionist, and gave us the correction. We spent two hours and made it half way through the directory that day. Before we published, we must have fixed hundreds of mistakes. The quality had never been better.

5. We delivered a printed copy to the print shop

Since the root cause of my group picking up this responsibility was a breakdown between the previous owner and the internal print shop, we knew we had to get this right. At the heart of the breakdown was the technology difference between the two groups, and could easily cause future problems. After much discussion we settled on an unexpectedly simple solution. We would give them a printed copy of the directory, one-sided and on full-size 8 ½ by 11 paper and they would shrink and print it double-sided. Really hard to mess that process up.

6. We surveyed our customers

I think it’s pretty obvious that we were excited by the changes and the quality of our final product. But what did everyone else think? What other good ideas had we not thought of? So I asked that a short survey be sent out. “Why in the world would we do that?” was the general response from the team. But I insisted and we got some ideas and some praise. My favorite was “It’s obvious that whoever designed the new directory was a traveler!”.

Spot on.

My Collection of SQL

Over the course of the last couple years I got to know Microsoft’s SQL Server. I’m no DBA, but with a good night’s sleep and lots of Google searches, I’ve been able to get the job done. I’m also rather fond of collecting those things I find useful, so just in case my list would do you some good, here it is. If it’s in italics, you need to replace that with your stuff. I hope I got most of that right. Some might run on one version of SQL Server, but not another.

No promises, no warranties. Here goes.

Row Counts for Every Table in a Database

SELECT o.name, rows

FROM sysindexes i join sysobjects o on o.id=i.id

WHERE indid < 2 and type='U'

ORDER BY rows DESC

Get Information on All Columns for All Tables in a Database

SELECT table_schema, table_name, column_name, ordinal_position,

      column_default, data_type, character_maximum_length

FROM information_schema.columns

The Last Time a Table was Updated For All Tables in a Database

SELECT DISTINCT OBJECT_NAME(object_id,database_id) as TableName,     last_user_update

FROM database name.sys.dm_db_index_usage_stats

WHERE database_id = DB_ID('database name')

GROUP BY OBJECT_NAME(object_id,database_id), last_user_update

ORDER BY TableName

Find String in Any Object in a Database

SELECT DISTINCT so.name

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE sc.TEXT LIKE '%string%'

Backup a Database

BACKUP DATABASE databasename

TO DISK = 'path\name.bak';

Restore Database to Another Location

RESTORE DATABASE databasename

FROM DISK = 'path\name.bak'

WITH RECOVERY,

MOVE 'databasename_Data' TO 'newpath\databasename_Data.MDF'

MOVE 'databasename_Log'  TO 'newpath\databasename_Data.LDF'

Copy Results from a Query to a Pipe-Delimited File

BCP "SELECT * FROM [database].[owner].[table_name]" queryout filename -Uuserid -Ppassword -t"|" -c -S server_name\instance

Change User Password

EXEC sp_password 'old password', 'new password', 'userid’

Select Rows Between Two Datetimes

SELECT *

FROM tablename

WHERE field BETWEEN '10/15/2015 00:00:00.00'

               AND '10/15/2015 23:59:59.999'

Select Yesterday's Data

SELECT *

FROM table

WHERE date_field >= dateadd(day,datediff(day,1,GETDATE()),0)

  AND date_field < dateadd(day,datediff(day,0,GETDATE()),0)

Count the Number of Occurances of Each Value

SELECT DISTINCT column_name, count(column_name) as CountOf

FROM tablename

GROUP BY column_name

Count Rows By Year

SELECT DISTINCT YEAR(datetime_field) as Year, COUNT(*) as Rows

FROM tablename

GROUP BY YEAR(datetime_field)

ORDER BY YEAR(datetime_field) DESC

Update Rows Based on a Time Difference in Minutes

UPDATE tablename

SET field = field

WHERE DATEDIFF(MINUTE,datetime,CURRENT_TIMESTAMP) < minutes

Replace String in a Field

UPDATE tablename
SET field = REPLACE(field, 'text', 'newtext')

Replace Substring in a Column

UPDATE tablename

SET field = CAST(REPLACE(CAST(field as NVarchar(4000)),'string1','string2') AS NText)

WHERE field LIKE '%string1%'

Delete Rows from a Table Between Two Datetimes

DELETE FROM tablename

WHERE date_field BETWEEN 'mm/dd/yyyy 00:00:00.00' AND 'mm/dd/yyyy 23:59:59.999'

Left Join

SELECT A.field1, A.field2, B.field3

FROM tablename1 A

LEFT JOIN tablename2 B

ON A.field1=B.field1