My Christmas 2009 story

This has nothing to do with SQL Server, my job or even my career, except that it has to do with writing. From time to time, i try to write something different, to see if i can. I want to share this here with you folks, because you are part of my personal life, just like my work bleeds into my personal life from time to time. So, I hope you will indulge me with this little deviation, and I hope that you enjoy the story.

My Christmas 2009 story.

funny thing happened while trying to order

Someone in the QA group encountered an issue with a process they were testing. As they dug into the error, they found that a single stored procedure was erroring. The reason they are testing is on a new instance of our core code and databases, running against SQL 2005. This is exciting to us, as we have pieces still running against SQL 2000. At first, the question is how this piece of code made it into the system, not working. Investigation was done by some members, and questions were raised. After a small amount of detective work, we realized that the code in SQL2005 was identical to SQL2000. But it didn't work in 2005. Armed with this realization, we were able to quickly see the query and the problem. It turns out that the original developers had aliased 1 of the fields. The select statement was returning a handful of fields, 2 of which were ID fields, but from different tables. To differentiate these, the second ID field had been aliased. It was no longer called ID, but was FilterConfigID. This is not the issue, but the start of the issue. Everywhere else in the statement, the table was referenced by name, not by alias. So Table.Field was the naming standard for this select statement. Once the developers got to the Order By statement, they simply followed suit, and called the field they were ordering by Table.Alias, using the single aliased field from the select statement. This worked in SQL2000. But ceased working in SQL20005. The query processor was parsing this, realizing that the fieldname (aliased from ID to ConfigFilterID) didnt exist in the table, and returned an error.
This was a fun little exercise in naming conventions, old code, differences in versions and so on. It was a welcome distraction from the current issue i was dealing with, and i believe all DB folks that were involved with is, on multiple fronts, had a fun few minutes digging into this, realizing the issue, and coming up with a solution.

Wanna see some of my favorite GoldenGate scripts?

We use GoldenGate for replication from our hot server, to our warm, search and dr. Lots of data is replicated, using many different GoldenGate processes. From time to time, we will stop a process, halting replicatino for a period. After starting it back up, replication will be latent for a period. Given time, it will catch up. But in the meantime, I get worried about the status. I also need to know if processes are running or stopped.

To aid in this purpose, I have created some standard sql scripts that will provide me the data needed.

To see which items are latent, we can check the ACONPROCSB table within the DirectorDB, and produce latency times for each item that is 3 minutes latent.

--Get Latent > 3:00
SELECT
SUBSTRING(LASTCHECKPOINTLAG, 1, 2) as [hours],
SUBSTRING(LASTCHECKPOINTLAG, 4, 2) as [minutes],
SUBSTRING(LASTCHECKPOINTLAG, 7, 2) as [seconds],
CommonName as [ServerName],
ProcessName,
ProcessType,
LastStatus,
LASTCHECKPOINTLAG
FROM DirectorDB.GGS_DIRECTOR.ACONPROCSB WITH(NOLOCK)
where LastCheckPointLag > '00:03:00'
order by LastCheckPointLag desc, [ServerName], ProcessType, ProcessName


To see which items are not running, we can check the ACONPROCSB table within the DirectorDB, and check the LastStatus value of each process.

--Get Non-Running
SELECT
CommonName as [ServerName],
ProcessName,
ProcessType,
LastStatus
FROM DirectorDB.GGS_DIRECTOR.ACONPROCSB WITH(NOLOCK)
where LastStatus <> 'Running'
and ProcessName not like 'R1T2T%'
order by LastCheckPointLag desc, [ServerName], ProcessType, ProcessName


I have as many of these scripts precoded to the appropriate director database, with the appropriate other 'where' clause values. I can quickly open up these scripts from within a project and execute them where they need to be executed, and tell the status of the systems. The results of this query are from the Director database, and it does contain a bit of latency itself, but its a quick and dirty solution to tell me status. Armed with this knowledge, I can easily tell where we stand.

I have used these queries to ease my mind on many many occasions. setting them up, testing them, and having them handy has helped me tremendously.